How do I use VBA to find the last row...it moves every week

[agiammo]

I have recorded a macro that will enter a formula in an empty column. It should enter the formula for every row that is populated. My macro specifies a range that doesn't change as the file grows. How do I edit my macro so it will 'find the last row'?

Here is my code:

Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=Data!RC[-5],""ok"",""WRONG"")"
Range("K2").Select
Selection.Copy
Range("K2:N2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K2:N495")
Range("K2:N495").Select
This message was edited by agiammo on 2002-05-10 13:43
This message was edited by agiammo on 2002-05-10 14:00

 

[NateO]

This code will duplicate your procedure to the last completed row in column F:

Sub cPy()
Dim i As Long
i = [f65536].End(xlUp).Row 'change F to correct column
[k2] = "=IF(F2=Data!F2,""ok"",""WRONG"")"
[k2].Copy Range("k2:n" & i)
End Sub

Hope this helps.

 

[agiammo]

Thanks! This works perfectly!