Deleting Last Rows in a Worksheet

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

As part of a macro we are working on, we want to delete
the last 22 rows of a worksheet.
Appreciate help with the code to do this.
TIA.
 
Try this for the activesheet
copy the code in a normal module

Sub test()
Dim Lr As Long
Lr = LastRow(ActiveSheet)
If Lr < 22 Then Exit Sub
ActiveSheet.Rows(Lr - 21 & ":" & Lr).EntireRow.Delete
End Sub

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

Sub DeleteLast22Rows()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("A" & LastRow & ":A" & LastRow - 21).EntireRow.Delete
End Sub

Regards

Trevor
 
I forgot to select this line when I copy
This is the last line from the function

End Function
 
Assume there will be a value in column A for each occupied row

set rng = cells(rows.count,1).End(xlup)

rng.offset(-21,0).Resize(22).Entirerow.delete
 
Back
Top