Thread: apply macro to all worksheets in the workbook

Hi,

I cannot figure out why this macro only runs on the active worksheet and not all worksheets in the workbook. The macro is to delete rows with a zero value in the end colum (in this case colum G). Can anyone advise? thanks

Code:

Sub Delete_zero_Rows()
    Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
   Dim lastrow As Long, r As Long
   lastrow = ws.UsedRange.Rows.Count
   For r = lastrow To 1 Step -1
    If UCase(Cells(r, 7).Value) = "0" Then Rows(r).Delete
    Next r
    Next ws
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

Because this line:

Code:

    If UCase(Cells(r, 7).Value) = "0" Then Rows(r).Delete

needs to be this:

Code:

    If UCase(ws.Cells(r, 7).Value) = "0" Then ws.Rows(r).Delete