Autofill Date Macro

[ladylissa21]

Hi! I'm trying my hand at editing a macro I recorded, and not having much luck. I just need to find a way to autofill the date just down to the last populated row instead of to the bottom of the page. Here's the pertinent section:

Columns("C:C").Select
Selection.InsertShift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "mm/dd/yy"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("C1").Select
Selection.AutoFillDestination:=Range("C:C")
Range("C:C").Select
Cells.Select

 

[Joe4]

You can look at column B to find the last row with data, and then populate column C down to that row with your formula, i.e.

    Dim lRow As Long
'   Find last row in column B with data
    lRow = Cells(Rows.Count, "B").End(xlUp).Row
'   Fill column C with current date
    Range("C1:C" & lRow).Formula = "=TODAY()"

Note: The code above only addresses the autofill part, and does not include the column insert and formatting, since that part was not problematic for you.

 

[Fluff]

Try

Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:C").NumberFormat = "mm/dd/yy"
Range("C1", Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Value = "=TODAY()"

This uses col A to find the last row

 

[ladylissa21]

Joe4 this worked perfectly! Thank you so much for the fast help!

 

[Joe4]

You are welcome.

Note that the first two lines of Fluff's post show you how you reduce that part from 3 lines down to 2, and also avoids using any "Select" statements, which helps speed up code performance.