Dynamic autofill VBA

Hi,

I have a document where I every month collect a lot of data in columns A:J.

In column K, I have a formula which I want to autofill from cell K2 until the last row (row 1 are just headers..) The formula is already typed into K2.

The volume of data varies from month to month. So I want the macro to be dynamic.

If there is data from row 2 until row 50 I want the formula to be autofilled until row 50 only.

If there is data from row 2 until row 70, autofilled until row 70...

Any suggestions how to do this?

Hi
try this macro

this assumes that only cells K1 and K2 are populated in column K - there is no need to delete old values
is this correct?

Sub Macro1()
Dim Coln As Long, Lastrow As Long

'establish the last populated cell in columns A:K
Lastrow = 2
For Coln = 1 To 11
    If ActiveSheet.Cells(Rows.Count, Coln).End(xlUp).Row > Lastrow Then Lastrow = ActiveSheet.Cells(Rows.Count, Coln).End(xlUp).Row
Next Coln

'Populate into column k
Range("K2").Copy Destination:=Range("K3", Columns("K").Rows(Lastrow).Address)

End Sub

Hope this can help in one or anoher way.

Thank you guys!

I actually used both of your solutions in my model, and both macros are helping me to do exactly what I wanted them to do! :-)