Find Null or Zero and Eliminate Entire Row if Found

[Bobaree]

The following sub allows me to delete entire rows if there is a cell with no data. Can anyone tell me how to get it to delete rows if a cell contains a "0" also. In other words, the VBA code would check for both and either would result in elimination of the row. Also, I currently am using this procedure seperately for several columns (E:M). If possible, I would like to combine the 10 different Subs into one Sub. Thanks for your help.

Sub Delete_Nulls_Column_E()
Dim LR As Long
Dim r As Long
With ActiveSheet
LR = .Range("A" & .Rows.Count).End(xlUp).Row
For r = LR To 2 Step -1
With .Range("E" & r)
If .Value = "" Then
.EntireRow.Delete

End If
End With
Next r
End With

 

[NeonRedSharpie]

Are the values actually 0 and "" or are they formulas creating 0 and ""?

 

[Bobaree]

Actual null and zero; not formulas.

 

[NeonRedSharpie]

Actual null and zero; not formulas.

Sub deleteNulls()

Dim endRow As Long


With Sheets("SHEET NAME GOES HERE") 'CHANGE ME TO YOUR SHEET NAME


    
    For y = 5 To 13 Step 1
        endRow = .Cells(Rows.Count, y).End(xlUp)
        For x = endRow To 2 Step -1
            Select Case .Cells(x, y).value
                Case "", 0
                    .Cells(x, y).EntireRow.Delete
                Case Else
            End Select
        Next x
    Next y
End With


End Sub

Try that. Maybe that will solve your issues.

 

[Bobaree]

We may be getting closer. Now it's getting stuck on "endRow = .Cells(Rows.Count, y).End(xlUp)". Notice that I added an Activate statement and that it's a Workbook rather than Sheet. I don't know if that affects the code you provided or not.

Sub deleteNulls()
Workbooks("Big, Safe Dividends Data.XLS").Activate
Dim endRow As Long
With Workbooks("Big, Safe Dividends Data.XLS")
For y = 4 To 13 Step 1
endRow = .Cells(Rows.Count, y).End(xlUp)
For x = endRow To 2 Step -1
Select Case .Cells(x, y).Value
Case "", 0
.Cells(x, y).EntireRow.Delete
Case Else
End Select
Next x
Next y
End With
End Sub

 

[NeonRedSharpie]

We may be getting closer. Now it's getting stuck on "endRow = .Cells(Rows.Count, y).End(xlUp)". Notice that I added an Activate statement and that it's a Workbook rather than Sheet. I don't know if that affects the code you provided or not.

Sub deleteNulls()
Workbooks("Big, Safe Dividends Data.XLS").Activate
Dim endRow As Long
With Workbooks("Big, Safe Dividends Data.XLS")
For y = 4 To 13 Step 1
endRow = .Cells(Rows.Count, y).End(xlUp)
For x = endRow To 2 Step -1
Select Case .Cells(x, y).Value
Case "", 0
.Cells(x, y).EntireRow.Delete
Case Else
End Select
Next x
Next y
End With
End Sub

How about because I'm an idiot?

[COLOR=#333333]endRow = .Cells(Rows.Count, y).End(xlUp).[U][B]ROW[/B][/U][/COLOR]

Color me embarrassed.

 

[Bobaree]

Thanks for your help.