Copy Formula Down until last row

[mickjjuk]

How would I copy a formula down a column until the last row.
I have data being output to a workbook and the number of rows change each
time I run the report. In cell M2 I wish to enter the formula =sum(L2-K2)
and then copy this down to the end cell in column M, so M3 = =sum(L3-K3), M4
= =sum(L4-K4) and so on

 

[marcus]

Hi Mick

Try this for size. Should do what you want.

take care

Marcus


Sub CopytoLast()
Dim lw As Long

lw = Range("L" & Rows.Count).End(xlUp).Row
Range("M2").Value = "=L2-K2"
Range("M2:M" & lw).FillDown

End Sub

 

[Rick Rothstein]

Here is another way to do it (note that FillDown is not used)...

Sub CopytoLast()
Dim LastRow As Long
Columns("M").ClearContents
LastRow = Cells(Rows.Count, "L").End(xlUp).Row
Range("M2:M" & LastRow).Formula = "=L2-K2"
End Sub

Also note that I clear the contents of Column M before putting the formula
into the cells... I do this just in case the data Column L is less than it
was the previous time the macro was run.