Delete rows using macro, loop to all sheets

[kev8279]

Hi guys,

I need a Macro that will look for 0 in column A and delete the row. I then need that to loop to do the same on all work sheets. I've been trawling the net for ages and can't come up with anything. Can anyone help please?

 

[Roli001]

Try this:

Sub deleteRow()

For Each sh In ActiveWorkbook.Sheets

    ActiveWorkbook.Sheets(sh.Name).Activate
    
    For i = [a65000].End(xlUp).Row To 1 Step -1
        If Cells(i, 1) = "0" Then Rows(i).Delete shift:=xlUp
    Next i

Next sh

End Sub

Good luck

 

[SydneyGeek]

I prefer to filter and delete, rather than looping. It's generally quicker, as long as you disable calculation first.

Sub Macro1()
    Dim Rng As Range
    Dim Sht As Worksheet
    
    Application.Calculation = xlCalculationManual
    
    For Each Sht In ActiveWorkbook.Sheets
        Sht.Activate
        Set Rng = Range("A1").CurrentRegion
        Rng.AutoFilter
        Rng.AutoFilter Field:=1, Criteria1:="0"
        Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Rng.AutoFilter
        Set Rng = Nothing
    Next Sht
    
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Denis

 

[kev8279]

That's great, thanks guys.

I've used the first one as some of the cells are hidden and grouped, however in order for this to work I need to unhide all of the cells. Is there a way to get around this?

Thanks