VBA Loop to delete rows with 0

[U16426]

Hi,

I have this vba below. It's designed to delete a row if the value in B is "0" within the given range.

' Delete Macro
'
 
Dim cell As Range
 
For Each cell In Range("A4:B8")
    If cell.Value = "0" Then
        cell.EntireRow.Delete
    End If
Next cell
'
End Sub

However, it only works in certain situations. For example, it will delete the "Fair" and "Very Good" rows but not the "Excellent" row as well. Any ideas?



Thank you

 

[mohadin]

Try

If cell.Value = "0" Or cell.Value = "Fair" Or cell.Value = "Very Good" Then

 

[U16426]

Try

If cell.Value = "0" Or cell.Value = "Fair" Or cell.Value = "Very Good" Then

Sorry my picture didnt add to show my example. The values in Column B will constantly change. So it needs to be dynamic based upon the values placed in Column B

Poor	3
Fair	0
Good	5
Very Good	0
Excellent	0

 

[Peter_SSs]

Try working from bottom to top.
How many rows of data do you actually have?

Sub Del_rows()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Range("B" & r).Value = 0 Then Rows(r).Delete
  Next r
  Application.ScreenUpdating = True
End Sub

 

[U16426]

Try working from bottom to top.
How many rows of data do you actually have?

Sub Del_rows()
  Dim r As Long
 
  Application.ScreenUpdating = False
  For r = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Range("B" & r).Value = 0 Then Rows(r).Delete
  Next r
  Application.ScreenUpdating = True
End Sub

Thank you. That worked.

It's only 5 rows of data from the table above

 

[Peter_SSs]

It's only 5 rows of data from the table above

It there are many thousands or rows that method can be a bit slow but for only a few it should be fine.

Thank you. That worked.

You're welcome. Thanks for the confirmation.

 

[johnnyL]

How about a no loop solution in case the column gets much larger? :

 Sub DelRowIfZeroInColumn()
'
    Application.ScreenUpdating = False
'
    Dim StartRowOfRange     As Long
    Dim StartColumnOfRange  As String
'
    StartColumnOfRange = "B"                                                                        ' <--- Set this to the start column of data
    StartRowOfRange = 1                                                                             ' <--- Set this to the start row of data
'
    Range(StartColumnOfRange & StartRowOfRange, Range(StartColumnOfRange & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=0
    Range(StartColumnOfRange & StartRowOfRange + 1, Range(StartColumnOfRange & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
'
    If Range(StartColumnOfRange & StartRowOfRange) = 0 Then Rows(StartRowOfRange).EntireRow.Delete
'
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub