Mastering advanced Excel macros has never been easier.
Allen Wyatt With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. Automate virtually any routine task and save yourself hours, days, maybe even weeks.
He is president of Sharon Parq Associates, a computer and publishing services company. Then, learn how to make Excel do things you thought were simply impossible!
This tip (5405) applies to Microsoft Excel 97, 2000, 2002, and 2003.
You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Skipping Rows when Filling.
If you need a series of dates where every date is the last day of the month you can use EODATE like this: If you need to generate a dynamic series of dates with a formula that increase by one day from a single start date, you can do so a very simple formula that simply adds 1 to each date.
How the formula works In the example, B6 is the hard-coded start...
However, if you put 1 into cell A1, leave cell A2 blank, select the two cells, and then drag downward, Excel assumes you want to increment the value and leave every other cell blank.
So, you end up with 1, blank, 2, blank, 3, blank, etc.
This establishes the pattern you want: number, blank, number, blank.