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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,436
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top