CODEWITHSUNDEEP
excelvba
user3063293
user3063293

Reputation: 41

For Each Loop Deleting Row if Cell = 0

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

Upvotes: 4

Views: 5868

Answers (3)

user121391
user121391

Reputation: 617

The other answers are correct and useful for most cases - AutoFilter is very fast, and a bottom-up loop does not have problems when deleting rows - but in certain cases you may need a loop that goes from top to bottom.

In this case there are two important steps:

  1. Decrease the counter variable manually after deletion because the row numbers change
  2. Use a while loop instead of a for loop, because Excel caches variables in for loops

Example with two columns and ten rows, where the first column contains various numbers and all rows with zero in this column should be removed:

Sub DeleteRowWithLoop()

    ' Define start and end row numbers
    Dim lastRow As Long
    Dim i As Long
    i = 1
    lastRow = 10

    ' Use while loop to enable dynamic change of lastRow
    While i <= lastRow

        ' Get current row to work on
        Dim currentRow As Range
        Set currentRow = Range(Cells(i, 1), Cells(i, 2))

        ' Delete whole row if first column contains zero
        ' Next and last cell numbers will have to be decreased
        If (Cells(i, 1).Value = 0) Then
            i = i - 1
            lastRow = lastRow - 1
            currentRow.EntireRow.Delete
        End If

        ' Increment while counter/current row number (even if nothing was deleted)
        i = i + 1
    Wend
End Sub

Upvotes: 1

nutsch
nutsch

Reputation: 5962

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

Upvotes: 0

Roberto
Roberto

Reputation: 2194

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

Upvotes: 5

Related Questions