Test for the last used row

  • Thread starter Thread starter sue
  • Start date Start date
S

sue

How can I test for the last used row, copy it and paste it in the next row...I can do the copy and
paste, but how do I acquire the last used row value?

Thank you,

Sue
 
Hi sue
to get the lst row you may use
lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row

to copy the last row try
ActiveSheet.Cells(Rows.count, "A").End(xlUp).entirerow.copy

to paste it in the next line try
ActiveSheet.paste destination:= activesheet.Cells(Rows.count,
"A").End(xlUp).offset(1,0)
 
Try this

Sub test()
Dim Firstrow As Long
Dim Lastrow As Long
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
Rows(Lastrow).Copy Rows(Lastrow + 1)
End Sub


Or use a function to be sure you have the last row with data

Sub test2()
Rows(LRow(ActiveSheet)).Copy Rows(LRow(ActiveSheet) + 1)
End Sub

Function LRow(sh As Worksheet)
On Error Resume Next
LRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
 
Back
Top