Running Delete Row Code For All Tabs In A Spreadsheet

[Mattster2020]

Afternoon All,

I am using the below code to delete rows from a tab in a spreadsheet, the code runs when a users clicks a button, my question is, how would I amend my code below to run for ALL tabs within the spreadsheet?

Private Sub CommandButton1_Click()
Dim lastrow As Long
Dim i As Long
lastrow = Range("A:J").SpecialCells(xlLastCell).Row
Range("E5").Select
i = 1
Do While i < lastrow
If ActiveCell = "0" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
End Sub

Any help would be appriecated.

Regards,

Matt

[p45cal]

quick (to adjust the code) and dirty way:Private Sub CommandButton1_Click()
Dim lastrow As Long
Dim i As Long
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
sht.Select
lastrow = Range("A:J").SpecialCells(xlLastCell).Row
Range("E5").Select
i = 1
Do While i < lastrow
If ActiveCell = "0" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
Next sht
End Sub
not so dirty, but quicker (in running time):Private Sub CommandButton1_Click()
Dim lastrow As Long
Dim i As Long
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
lastrow = sht.Range("A:J").SpecialCells(xlLastCell).Row
For i = lastrow To 5 Step -1
If sht.Cells(i, "E") = "0" Then
sht.Rows(i).Delete
End If
Next i
Next sht
End Subnote that this line:
lastrow = sht.Range("A:J").SpecialCells(xlLastCell).Row
ignores your range specified and returns the row of the last cell on the entire sheet (in xl2003 anyway).