Sub FindAndUseLastRow()
Dim rw As Long
if not isempty(Range("A" & rows.count)) then
rw = rows.count
else
rw = Cells(Rows.Count, "A").End(xlUp).Row
End if

Range("L2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,1,""-"")"
Range("M2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,2,""-"")"
Range("N2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,3,""-"")"
Range("L2:N2").Select
Selection.AutoFill Destination:=Range("L2:N" & rw), Type:=xlFillDefault
Range("L2:N" & rw).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Regards,
Tom Ogilvy

"VexedFist" wrote:

Tom Olgivy provided this to find the Last row in a spreadsheet that has
data in it, for another user here on the Forums.

Can anyone tell me how I can use the value "rw' to replace N1000 in the
below Macro?

Sub FindAndUseLastRow()
Dim rw As Long
if not isempty(Range("A" & rows.count)) then
rw = rows.count
else
rw = Cells(Rows.Count, "A").End(xlUp).Row
End if

Range("L2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,1,""-"")"
Range("M2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,2,""-"")"
Range("N2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,3,""-"")"
Range("L2:N2").Select
Selection.AutoFill Destination:=Range("L2:N1000), Type:=xlFillDefault
Range("L2:N1000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub