Search code examples
excelvbadrag-and-dropfill

Auto Fill Sequential Dates Until Next Row Down has Value

I am trying to pull down the date column to fill in the next sequential dates in the blank cells until the next cell down has a value in it. For example, the blank cells in the highlighted date range should read 3/23/2019 & 3/24/2019 (see screenshot, Column C):

Excel Table Screenshot

Here is the code that I have crudely put together. But I am so new to this, I am not sure where I am going wrong. In my logic, I execute the code starting from Range C2:

Sub fillInDates()

Dim cellEndRange As Range
Dim cellStartRange As Range


Selection.End(xlDown).Select

ActiveCell.Offset(RowOffset:=-1, ColumnOffset:=0).Activate

cellEndRange = ActiveCell

Selection.End(xlUp).Select

cellStartRange = ActiveCell

cellStartRange.AutoFill Destination:=cellStartRange & cellEndRange
Solution

  • This can be done without VBA code.

    • select column C
    • hit F5 or Ctrl-G to open the Go To dialog
    • click Special
    • tick Blanks and hit OK
    • now all blank cells are selected. Without changing the selection, type a = character
    • hit the up arrow to reference the cell above the current cell
    • type +1 to add one day to the date from the cell above
    • hold down the Ctrl key and hit Enter.

    Now that formula is in all the cells that were previously blank. You can use copy/Paste Values to replace the formula with the values it calculated.