Find Null or Zero and Eliminate Entire Row if Found

Bobaree

New Member
Joined
Aug 13, 2014
Messages
41
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Code:
Sub deleteNulls()

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


End Sub
 
Upvote 0
Or
Code:
If .Value = ""  Or .Value = 0 Then
]/Code]
 
Upvote 0
Thanks gsistek. That was a simple solution and it worked. Currently I am repeating that Sub 10 times for 10 different columns. Is there a simple way to combine your code into one Sub to take care of all 10 columns? NeonRedSharpie provided code for that, but it gave me a Compile error: Invalid Next control variable reference. I suspect I was supposed to put something else in the Next command, but as a beginner, I didn't know what to do.
 
Upvote 0
Thanks for your help NeonRedSharpie. It looks like a good solution, but I assume you gave me the first part and thought I knew how to fill in the Next x part. As a beginner, I didn't know how to address that. I got a Compile error: Invalid Next control variable reference. Can you tell me how to fill in the rest of the code. I have a solution to delete these columns another way, but I have to repeat the Sub 10 times, and it takes a "long time" for it to execute.
 
Upvote 0
Code:
Sub deleteNulls()

For y = 5 To 13 Step 1
    For x = Cells(Rows.Count, y).End(xlUp) To 2 Step -1
        Select Case Cells(x, y).value
            Case "", 0
                Cells(x, y).EntireRow.Delete
            Case Else
        End Select
    Next x
Next [COLOR=#ff0000][U][I][B]x[/B][/I][/U][/COLOR]​  y


End Sub

I just fubarred the next control. Changing the final "Next x" to "Next y" should fix that.
 
Upvote 0
Thanks for your input NeonRedSharpie. I didn't get the Compile error, but it didn't delete the rows. Any other possibilities? Thanks again.
 
Upvote 0

Forum statistics

Threads
1,211,882
Messages
6,104,557
Members
447,915
Latest member
jpearson

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
Back
Top