AutoFill Macro Assistance

[cartoonjess]

Hello Everyone!

I would appreciate assistance on a Macro I am working on. I added Column D, into which data will be concatenated from Columns A, B, and C. I would like Column D to AutoFill with the Concatenate Formula in Cell D2 until the last cell of Column D that corresponds with the last populated cell in Column C. The command currently looks like this (this was based on today's report, but each day the length of the report changes) :

Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D71")

I would also like to apply formatting and the like to the entire populated worksheet from Columns A to D (starting with Cell A1). The current range is:

Range("A1:D71").Select

Is there an easy way to make the range from the starting cell to the last populated cell in the column? I appreciate any assistance that can be provided and hope you have a great day!

 

[PCL]

Try

Option Explicit


Sub FillDownMacro()
Dim LR  As Long
    LR = Cells(Rows.Count, "C").End(3).Row
    Range(Cells(2, "D"), Cells(LR, "D")).FillDown


    Cells(1, "A").Copy
    Range(Cells(1, "A"), Cells(LR, "D")).PasteSpecial Paste:=xlPasteFormats
End Sub

 

[Michael M]

If you post the formula you are using, it can be inserted into the code without the need for FillDown !!