For Each Loop Deleting Row if Cell = 0

user_3063293 0
Viewed 5518

I'm trying to write a macro that will delete a row if a cell = 0 in the range given. The problem I am coming across is when the For Each Loop runs it will find the cell and delete the row but if the row below it also had a 0 it ends up getting skipped by the code since the code has moved onto the next range. I'm looking to have a macro that will find 0 in a range of cells and will loop on that range that had a 0 until the that cell is greater than 0. I've got this as a work in progress... 

Sub Pub_Clean()

Dim IRange As Range
Dim VRange As Range
Set VRange = Range(ActiveSheet.Range("b3"), ActiveSheet.Range("b3").End(xlDown))
    For Each IRange In VRange
    If IRange = 0 Then
    IRange.EntireRow.Delete
    End If
    Next IRange
End Sub
vba excel
3 Answers

When deleting, the typical approach is to start at the bottom and loop up. This requires an index-specified loop, rather than FOR EACH. Here are some details:

For I=TotalRows To 1 Step -1 
    Set c = Range("C" & I) 
     ' code to check for criteria and delete if required
Next I

taken from here: http://www.ozgrid.com/forum/showthread.php?t=56516

user_26831040

The fastest way of doing things to rows is to run an autofilter and use specialcells(xlcelltypevisible). A loop, especially one that deletes rows, is much slower. In your case, here's the update: 

Sub Pub_Clean()

Dim VRange As Range

Set VRange = Range(ActiveSheet.Range("b2"), ActiveSheet.Range("b2").End(xlDown))

with VRange
   .autofilter
   .autofilter field:=1, criteria1:="0"
   .resize(.rows.count-1).offset(1).specialcells(xlcelltypevisible).entirerow.delete
   .autofilter
End with

End Sub
user_16903920
Related