Macro to Auto Fill Dates in Column

**erock24** · 01-14-2009, 11:18 AM

I have a startdate F1 and an end date G1. Is there a macro that can list all dates by day within that range into column I7:down. A macro solution would be ideal.

Thank you very much for your time and help.

**ExcelFed** · 01-14-2009, 11:35 AM

If you use this formula in I7 and drag down, it should work. This assumes I6 is blank

=IF(I6="",G1-(G1-F1-1),IF(I6>=$G$1,"",I6+1))

**erock24** · 01-14-2009, 02:37 PM

Thank you for the reply. The formula works. However, I incorrectly stated my needs. Turns out I need to scratch the start date, end date stuff and proceed as described below:

I need a macro that will identify the last cell in I and fill down the dates to the end of the year. For example, If the last cell in I were to be I188 and it = 6/30/2008, I need I189 to = 7/1/2008, I190 to = 7/2/2008, etc.

Is there a macro that could do this?

**VBA Noob** · 01-14-2009, 02:59 PM

This worked for me.

Note no error checking if you run it twice

Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
        LastDate = DateValue("31/12/" & Year(.Value)) - .Value
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate), Type:=xlFillDefault
    End With

VBA Noob

**erock24** · 01-14-2009, 03:11 PM

That works perfect thank you. I added an error feature.

Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
        LastDate = DateValue("31/12/" & Year(.Value)) - .Value
On error resume next
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate), Type:=xlFillDefault
    End With

**VBA Noob** · 01-14-2009, 03:11 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**erock24** · 01-23-2009, 03:03 PM

How could I edit the macro to only fill to the last day of the month instead of the year?

Thank you.

**erock24** · 01-23-2009, 07:48 PM

How do I make it always default to the last day of the month. Instead of having the "31" hard coded in?

Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
        LastDate = DateValue("31/" & Month(.Value) & "/" & Year(.Value)) - .Value
On Error Resume Next
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate), Type:=xlFillDefault
    End With

**erock24** · 01-26-2009, 10:10 AM

I'm checking here to see: http://www.mrexcel.com/forum/showthr...08#post1816808

**erock24** · 01-26-2009, 12:02 PM

by jonmo1: http://www.mrexcel.com/forum/showthr...=1#post1816939

Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
    LastDate = (DateSerial(Year(.Value), Month(.Value) + 1, 1) - 1) - .Value
On Error Resume Next
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate)
    End With

works perfect