Macro to Delete Rows with 0 in Multiple Columns

[JHCali]

Hello,

I need help with a macro that will delete rows where 0 is found in certain columns. The range is row 8 to row 1127. The columns that I need the macro to look at are A through E.

Below is a sample data set with a row range of 1 to 50 and a column range of A through E. The "---" are entries in column D.

I need the macro to delete all rows where a 0 appears anywhere in columns A through E. So if done correctly on the sample set below, only 4 rows will remain and all the rest will be deleted.

Also, I don't know if this is relevant, but the 0 values below are not due to 0 being entered into the cell. Rather, the 0 values are there because a cell is linked to another cell which has nothing entered.

Thank you in advance for your help.

<table x:str="" style="border-collapse: collapse; width: 272pt;" width="363" border="0" cellpadding="0" cellspacing="0"><col style="width: 56pt;" width="75"> <col style="width: 72pt;" width="96"> <col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 56pt;" width="75" height="17">Decorators</td> <td class="xl24" style="width: 72pt;" width="96">Decorating</td> <td class="xl24" style="width: 48pt;" width="64">Customer</td> <td class="xl24" style="width: 48pt;" x:str="'---" width="64">---</td> <td class="xl25" style="width: 48pt;" x:num="39814" width="64">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24">I&D</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24">Modular</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24">Official</td> <td class="xl24">Electrical</td> <td class="xl24" x:str="'---">---</td> <td class="xl25" x:num="39814">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24">Electrical</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24">Stagehand</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24">Official</td> <td class="xl24">HS</td> <td class="xl24" x:str="'---">---</td> <td class="xl25" x:num="39814">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24">HS</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24">Elec HS</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24">Official</td> <td class="xl24">Freight</td> <td class="xl24" x:str="'---">---</td> <td class="xl25" x:num="39814">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24">Freight</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24">Small Package</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> </tbody></table>

 

[Tom Urtis]

Test this on a copy of your workbook but it should do what you want, if your data looks the way you presented it.

Sub Test()
Dim iCol%
For iCol = 1 To 5
With Columns(iCol)
.Replace What:="0", Replacement:="", LookAt:=xlWhole
.SpecialCells(4).EntireRow.Delete
End With
Next iCol
End Sub

 

[JHCali]

Hi Tom,

That macro worked great on my sample. But when I tried it on the actual worksheet, I got a Runtime error '9': Subscript out of range error.

The actual worksheet has 6 columns, so I changed your code to "For iCol = 1 to 6".

When I went to debug the error, it highlighted the entire row beginning with .Replace.

Do you know what the problem might be?

Also, is there any way to modify your macro so that it looks only at rows 8 through 1127?

Thanks so much for all your help.

 

[Tom Urtis]

Knowing that, try this assuming column G is available:

Sub Test2()
Application.ScreenUpdating = False
With Range("G8:G1127")
.Formula = "=COUNTIF(RC1:RC6,0)=0"
.Value = .Value
.Replace What:="FALSE", Replacement:="", LookAt:=xlWhole
.SpecialCells(4).EntireRow.Delete
End With
Columns(7).Clear
Application.ScreenUpdating = True
End Sub

 

[JHCali]

I did it with column L, as that is the first column with no data. However, I got the same subscript out of range error, with the code row beginning with .Replace being highlighted upon debugging.

I looked at what the macro did, and it filled range (L8:L1127) with "TRUE" and "FALSE" correctly.

There's just the hurdle of the rows with "FALSE" being deleted.

 

[Tom Urtis]

I've tested all these before posting them and do not get an error.

This will find the next column and delete rows where a zero exists in range A8:F1127. If you get an error, maybe you have merged cells or protected cells or something else going on because it works for me.

Sub Test3()
Dim NextColumn%
NextColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
Application.ScreenUpdating = False
With Range(Cells(8, NextColumn), Cells(1127, NextColumn))
.Formula = "=COUNTIF(RC1:RC6,0)=0"
.Value = .Value
.Replace What:="FALSE", Replacement:="", LookAt:=xlWhole
.SpecialCells(4).EntireRow.Delete
End With
Columns(NextColumn).Clear
Application.ScreenUpdating = True
End Sub