CODEWITHSUNDEEP
vbaexcel
user4373888
user4373888

Reputation: 53

Delete rows containing zero on all but a couple of worksheets

I am trying to create a macro to delete rows containing a 0 value in column L on all but a few workbooks in my worksheet. I have found a macro that can do this on the activesheet and I tried to nest it in function that loops through the desired sheets but it still only delete's the zero's in the active sheet. Can anyone see what i am doing wrong?

Here is the code:

Sub nul_waarden_verwijderen()

 Dim WS As Worksheet
 Dim lngMyRow As Long
 Application.ScreenUpdating = False

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Name <> "TB per 29122014" And WS.Name <> "SCOA" And WS.Name <> "105 Enterprise klad" And WS.Name <> "105 rekenblad" And WS.Name <> "list" Then         
            For lngMyRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row To 2 Step -1
                If Val(Cells(lngMyRow, "L")) = 0 Then
                   Rows(lngMyRow).EntireRow.Delete
                End If
            Next lngMyRow
        End If                                  
    Next WS

End Sub

Upvotes: 0

Views: 118

Answers (1)

EngJon
EngJon

Reputation: 987

You have to use a qualifier in front of .Cells and .Rows in order for it to work on a specific worksheet. Without this qualifier, those two refer to ActiveSheet.Cells and ActiveSheet.Rows.

For Each WS In ActiveWorkbook.Worksheets
    If WS.Name <> "TB per 29122014" And WS.Name <> "SCOA" And WS.Name <> "105 Enterprise klad" And WS.Name <> "105 rekenblad" And WS.Name <> "list" Then
        For lngMyRow = WS.Cells(Cells.Rows.Count, "E").End(xlUp).Row To 2 Step -1
            If Val(WS.Cells(lngMyRow, "L")) = 0 Then
                WS.Rows(lngMyRow).EntireRow.Delete
            End If
        Next lngMyRow
    End If
Next WS

Upvotes: 1

Related Questions