Delete rows using macro, loop to all sheets

kev8279

New Member
Joined
Oct 12, 2009
Messages
29
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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Roli001

Board Regular
Joined
Jul 29, 2004
Messages
130
Try this:

Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
I prefer to filter and delete, rather than looping. It's generally quicker, as long as you disable calculation first.
Code:
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

New Member
Joined
Oct 12, 2009
Messages
29
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,326
Messages
5,624,018
Members
416,006
Latest member
PCaffrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top