CODEWITHSUNDEEP
excelvba
yeboye
yeboye

Reputation: 19

VBA delete cell in for each loop

I'm trying to delete every cell, and a cell to the left of it, if the value of the cell is 0. (Also, to set the interior color to no fill if the value of the cell is greater than 0.)

This is my code so far


For Each cell In Range("I2:I" & LastTransaction)
    If cell.Value = 0 Then
        Range(cell.Offset(0, -1).Address, cell.Address).Delete Shift:=xlUp
    ElseIf cell.Value > 0 Then
        cell.Interior.ColorIndex = 0
    End If
Next cell

The problem here is that, every time the cells are deleted, AND SHIFTED UPWARDS, the for each loop doesn't take the upward shift into account, and skips the next cell in the range.

Upvotes: 1

Views: 913

Answers (2)

JMP
JMP

Reputation: 4467

The main issue is that, when you loop through the range, each cell refers to a particular cells on the sheet.

So your loop loops through I2, I3, I4, .... If you delete a row, all the other rows are moved up, and what was in cell Ix is now in cell I(x-1), but the loop will be at cell Ix, and so a whole row will have avoided being processed.

One solution is to store all the rows that are to be deleted in a Collection, and then delete the collection afterwards.

'data
Cells.Clear
For i = 1 To 15
    Cells(i, 1) = Int(Rnd * 4)
    Cells(1, i + 2) = Cells(i, 1) 
Next i

'code
Dim tbd As New Collection 'to be deleted collection
For Each c In [a1:a15]
    If c = 1 Then tbd.Add c 'adds a row that is to be deleted
Next c
For Each c In tbd
    c.Delete 'deletes all rows in tbd
Next c

The first part of the code creates some sample data to process. Then it defines a new Collection, adds the rows to it that are to be deleted (anything with a value 1 is this case), and then deletes them from the collection.

Upvotes: 0

Spencer Barnes
Spencer Barnes

Reputation: 2877

As per @JvdV's comment, when deleting in a loop you need to do it back to front (or in this case bottom to top), using Step -1.
In this case your For loop would look something like;

For x = LastTransaction to 2 Step -1
    If Range("I" & x).Value = 0 then
        Range("H" & x & ":I" & x).Delete Shift:=xlUp
    ElseIf Range("I" & x).Value > 0 Then
        Range("I" & x).Interior.ColorIndex = 0
    End If
Next

Upvotes: 1

Related Questions