Why my macro doesn't delete all the rows (VBA)

Question

i have this macro it is supposed to delete all the cells that doesnt have a background set to them, but when I execute the macro if two or more contiguous cells doesnt have a background it only deletes one of them, here is the code:

Sub Macro1()
        Dim a As Range

        Set a = Hoja1.Range("A1:A12")
        For Each cell In a
            If cell.Interior.ColorIndex = xlNone Then
                cell.EntireRow.Delete
            End If
        Next
End Sub

Tim Williams · Accepted Answer · 2013-12-23 18:07:35Z

4

Sub Macro1()
    Dim a As Range, x As Long

    Set a = Hoja1.Range("A1:A12")

    For x = a.cells.count to 1 Step -1
        with a.cells(x)
            if .Interior.ColorIndex = xlNone Then .EntireRow.Delete
        End With
    Next x
End Sub

answered Dec 23, 2013 at 18:07

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alejandro Over a year ago

This worked flawlessly than you! but can you explain me why my code doesn't worked please??

guitarthrower Over a year ago

when deleting rows, you have to work from the bottom up because of how Excel deletes then moves to the next. My guess is that you had 2 consecutive rows that met the criteria and the second one was getting skipped. Working from the bottom up gets around that issue.

Collectives™ on Stack Overflow

Why my macro doesn't delete all the rows (VBA)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related