Conditionally delete cells using VBA

Dear Experts:

I got a big table where lots of cells/entries in Column D are red shaded (RGB 150, 54, 52).

Now I would like to achieve the following using a macro:

The macro is to loop thru all cells of Column D
If the macro hits a red shaded cell (RGB 150, 54, 52)  the macro is to delete the cell above, e.g.

a red-shaded cell occurs in Cell D18. The cell contents of D17 is to be deleted.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Assuming you meant clear:

Sub foo()
    Dim lastRow               As Long
    Dim Cell                  As Range
    
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    With ActiveSheet.Range("D1:D" & lastRow)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=RGB(150, _
                                             54, 52), Operator:=xlFilterCellColor
        If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            For Each Cell In .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Cells
                Cell.Offset(-1).ClearContents
            Next Cell
        End If
        .AutoFilter
    End With

End Sub

Open in new window

0
 
Rory ArchibaldCommented:
Do you want the cell above it cleared, or actually deleted? They are not the same thing. :)
0
 
Rob HensonFinance AnalystCommented:
How is the Red shading occurring, is it manual formatting or Conditional?

Does the red shading occur because of certain conditions? If so you could apply Condotional formatting to the cells checking the condition/value of the cell below and formatting the cell accordingly. You can then filter based on this new shading and delete the cells left visible. Highlighting the visible area and clearing or deleting rows will only affect the visible area.

Thanks
Rob H
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Rob, thank you very much for your valuable help, but as a matter of fact, Rory delivered exactly what I was looking for.

Nevertheless thank you very much for your great help. Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
As always, great professional job. Thank you very much for your swift support. Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.