VBA that selects specific range of cells and deletes only the highlighted cells and shifts up-CodePudding

I'm new at using VBA. I'm creating a macro that scans through each cell within a range to detect any cells filled with yellow, deleting them, and shifting up. Then it should move to the next cell in the data table.

I'm getting "Run-time error '1004': Delete method of Range class failed", which I think is about the Range object I declared, but I'm not sure how I should properly implement this or if there's a better way to execute this VBA? This is what I have so far.

Sub DeleteHighlight()
Dim cell As Range
For Each cell In Range("A6:O3863")
    If ActiveCell.Interior.Color <> RGB(255,255,0) Then
       ActiveCell.Delete Shift:=x1ToUp
       Exit For
    End If
Next cell
End Sub

CodePudding user response：

@Warcupine is right that you're going to end up skipping rows. Instead, you should loop from the bottom to the top.

Sub DeleteHighlight()

Dim lRow As Long

'find last row
lRow = Range("A1").CurrentRegion.Rows.Count

'loop backwards
For i = lRow To 1 Step -1
If Cells(i, 1).Interior.Color = RGB(255, 255, 0) Then
Rows(i).Delete
End If
Next

End Sub

CodePudding user response：

Scan upwards to avoid missing the cell below the deleted one. (Untested)

Sub DeleteHighlight()
    Dim r as Long, c as Long
    For r = 3863 to 6 Step -1
        For c = 1 To 15 'O
            If Cells(r,c).Interior.Color = RGB(255,255,0) Then
                Cells(r,c).Delete Shift:=xlToUp
            End If
        Next
    Next
End Sub