macro to delete last 5 rows

**JulianActon** · 09-13-2006, 06:44 AM

hi

every week I get a new spreadsheet created by CRM software. The CRM adds 5 superfluous lines at the foot of the sheet, in column A. I'm trying to automate removal of these 5 lines.

I created a simple macro to do this as the footer text is the same every week, so is easy enough to locate. The issue is that the size of the spreadsheet varies from week to week. Does anyone have any code to automate the removal of the last n rows of a sheet?

many thanks.

Julian

**raypayette** · 09-13-2006, 01:56 PM

Function LastRow()
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
End Function

Sub DeleteRows()
Rows(LastRow - 4 & ":" & LastRow).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub