CODEWITHSUNDEEP
excelvbadrag-and-dropfill
Erik Zometa
Erik Zometa

Reputation: 5

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

Upvotes: 0

Views: 904

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

Try, you can work with below.

Sub test()
    Dim Ws As Worksheet
    Dim vDB As Variant
    Dim rngDB As Range
    Dim myDate As Date
    Dim i As Long
    
    Set Ws = ActiveSheet
    
    With Ws
        Set rngDB = .Range("c2", .Range("c" & Rows.Count).End(xlUp))
    End With
    vDB = rngDB
    For i = 1 To UBound(vDB, 1)
        If vDB(i, 1) = "" Then
            myDate = myDate + 1
            vDB(i, 1) = myDate
        Else
            myDate = vDB(i, 1)
        End If
    Next i
    rngDB = vDB
End Sub

Upvotes: 0

teylyn
teylyn

Reputation: 35990

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.

Upvotes: 1

Related Questions