Continue formula in entire column, indefinitely

[Lifterlady16]

I'm very new to VBA and have not found the answer in other posts. Hoping you can assist.

I need the formula provided below to continue past cell 83, as I'm going to be reusing this macro on other data that may be larger than 83 lines. How do I essentially tell it to read ALL cells in the column?

[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("L1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "Month"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("L2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "=MONTH"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("L2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("L2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Selection.AutoFillDestination:=Range("L2:L83")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("L2:L83").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000][CODE][/COLOR][/SIZE][/FONT]

 

[Joe4]

It looks like your formula is referencing column J.
So, I assume that means that we can look at column J to determine exactly how far we need to do down.

Try this:

Dim lastRow As Long
lastRow = Cells(Rows.Count, "J").End(xlUp).Row


Range("L1").FormulaR1C1 = "Month"
Range("L2:L" & lastRow).FormulaR1C1 = "=MONTH(RC[-2])"

 

[Jambi46n2]

Another way using the activecell you've selected.

If your formula is present in L2 this will fill down to the last cell of data in Column J.

You can modify the "J" in the code to be "A" or any other column as far as you want to go.

    Range(ActiveCell.Address & ":" & Cells(Cells(Rows.Count, "J").End(xlUp).Row, ActiveCell.Column + 0).Address).Select
    Selection.FillDown

 

[Lifterlady16]

I really like this version with the "filldown". I have multiple formulas to fill down, so this is ideal. I'll give it a shot!

 

[Lifterlady16]

Jambi, I would squeeze you with the biggest hug. It worked PERFECTLY! Thank you!

 

[Jambi46n2]

Happy to help!

This forum has helped me so many times when I've been in a bind.

So it's nice to be able to pay it forward when I can.