How to Delete Rows Based on Background Color in Excel

Excel is a powerful spreadsheet programme that allows you to easily organise and analyse data. Many users are faced with the requirement to delete rows based on certain criteria. This course will focus on eliminating rows depending on cell background colour. When you have color−coded data or wish to filter out specific rows based on their aesthetic look, working with backdrop colours can be really handy. Rather than manually scrolling through your spreadsheet and eliminating rows one by one, we'll look at an automated method that can save you time and effort.

Deleting Rows in Excel Based on Background Color

Here, we will first create a VBA module, then choose the cell with the background colour to delete. So let us see a simple process to learn how you can delete rows based on background colour in Excel.

Step 1

Consider an Excel sheet where you have cells with multiple background colors, as shown in the following screenshot:

First, right−click on the sheet name and select View code to open the VBA application.

Right click > View code.

Step 2

Then click on Insert, select Module, and copy the below code into the text box.

Insert > Module > Copy.

Example

Sub DeleteRows()
    Dim rngCl As Range
    Dim xRows As Long
    Dim xCol As Long
    Dim colorLg As Long
    On Error Resume Next
    Set rngCl = Application.InputBox _
        (Prompt:="Select a cell with the background color to be deleted", _
        Title:="Delete Based Color", Type:=8)
    On Error GoTo 0
    If rngCl Is Nothing Then
        MsgBox "User cancelled operation." & vbCrLf & _
        "Processing terminated", vbInformation, "Delete Rows Color"
        Exit Sub
    End If
    colorLg = rngCl.Interior.Color
    Application.ScreenUpdating = False
    With ActiveSheet.UsedRange
        For xRows = .Rows.Count To 1 Step -1
            For xCol = 1 To .Columns.Count
                           If .Cells(xRows, xCol).Interior.Color = colorLg Then
                    .Rows(xRows).Delete
                    Exit For
                End If
            Next xCol
        Next xRows
    End With
    Application.ScreenUpdating = True
End Sub

Step 3

Then click F5 to go to the module, select the cell with the background colour you want to delete, and click OK to delete all the cells with matching background colours.

F5 > Select cell > Ok.

Note: If you want to delete only cells in a single column, use the blow code.

Example

Sub deleterow()
   Dim xRg As Range, rgDel As Range
    For Each xRg In ThisWorkbook.ActiveSheet.Range("A2:A10")
        If xRg.Interior.ColorIndex = 20 Then
            If rgDel Is Nothing Then
                Set rgDel = xRg
            Else
                Set rgDel = Union(rgDel, xRg)
            End If
        End If
    Next xRg
    If Not rgDel Is Nothing Then rgDel.EntireRow.Delete
End Sub

In the code, A2:A10 is the range of columns.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can delete rows based on background colour in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 20-Jul-2023

332 Views

Kickstart Your Career

Get certified by completing the course

Get Started