Delete the bottom 3 rows in a table

Hi Davinder,

Thank you for your question.

Try this bit of code on some test data first and make sure it is doing exactly what you wanted. It will prompt and ask you how many rows need to be deleted. The default has been set to three relating to your request.

Sub TrimAllSheets()

Dim cs As String
cs = ActiveSheet.Name
Dim y As Integer
y = Application.InputBox("How many bottom rows do you wish to delete?", _
Default:=3, Type:=1) 'Change default number (3) if desired.
If MsgBox("Are you sure you wish to delete " & y & " rows from the bottom of ALL sheets?", _
vbYesNo, "Trim ALL Sheets") = vbNo Then Exit Sub
Application.ScreenUpdating = False
Dim r As Range, s As Range
Dim ws As Worksheet
On Error Resume Next 'Error handler
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Set r = ActiveSheet.Range("A65536").End(xlUp).Offset(-y + 1)
Set s = ActiveSheet.Range("A65536").End(xlUp)
If ActiveCell.Row < 10 Then GoTo circumv 'Not to delete Headers
Range(r, s).EntireRow.Delete
circumv:
Next ws
Sheets(cs).Activate
Application.ScreenUpdating = True

End Sub

I hope this answers your question.

Let me know.

Regards

Simon

Hi Davinder,

Just a quick addition to the last email the code deletes the last three rows from every sheet in the workbook so you can adapt the code and remove the references relating to the looping procedure that loops through all the sheets.

Regards

Simon