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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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
 

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
Change
Code:
If .Value = "" Then
to
Code:
If .Value="" Or .Value=0 Then
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Or
Code:
If .Value = ""  Or .Value = 0 Then
]/Code]
 

Bobaree

New Member
Joined
Aug 13, 2014
Messages
41

ADVERTISEMENT

Thanks for the help.
 

Bobaree

New Member
Joined
Aug 13, 2014
Messages
41

ADVERTISEMENT

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.
 

Bobaree

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

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

Bobaree

New Member
Joined
Aug 13, 2014
Messages
41
Thanks for your input NeonRedSharpie. I didn't get the Compile error, but it didn't delete the rows. Any other possibilities? Thanks again.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,956
Members
430,330
Latest member
drAli77

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