Using Excel VBA to Delete Rows Based on the Cell Value – 5 Examples

Enabling the Developer Tab in Excel

If the Developer tab on the Excel Ribbon is hidden, you need to enable it through the settings options.

Launching the VBA Editor

• Press Alt + F11 to open the Microsoft Visual Basic Applications
• Click Insert and select Module.

• A module is displayed.

The sample dataset contains the “Net Sales of Products”: ID, Name of the Salesman, Product, and Net Sales.

Example 1 – Combining an If Statement and a For Loop to Delete Rows

The VBA code will search for the product Cable. If it finds the text, it will delete the rows.

• Create a new module in the VBA window.
• Enter the following code in the module.

'Combination of If Statement and For loop
'Declaring Sub-procedure
Sub Delete_Rows_with_If_and_For()
'Declaring variables
Dim cell As Range
For Each cell In Range("B5:E11")
    'We will delete rows containing Cable
    If cell.Value = "Cable" Then
        cell.EntireRow.Delete
    End If
Next cell
End Sub

A For loop is used within B5:E11. If the cell value is Cable ,it will delete the rows.

• Press F5 or click Run to run the code.

Example 2 – Utilizing the Filter Feature to Delete Rows with Excel VBA

Use the Filter feature in Excel VBA to filter the data based on cell values and delete the rows that meet the filter criteria.

• Create a new module in the VBA window.
• Enter the following code in the module.

'Use of Filter Feature in VBA
'Declaring Sub-procedure
Sub Delete_Rows_Using_Filter_Feature()
'Declaring variables
Dim WB As Worksheet
'Set the worksheet where you want to apply the VBA
  Set WB = ThisWorkbook.Worksheets("Filter Feature with VBA")
  WB.Activate
  'Delete any filters if exists
  On Error Resume Next
    WB.ShowAllData
  On Error GoTo 0https://www.exceldemy.com/delete-unfiltered-rows-in-excel-vba/r
'Provide criteria through which you want to filter
  WB.Range("B5:E11").AutoFilter Field:=3, Criteria1:="Cable"
'Delete rows from the Range which matches the criteria
  Application.DisplayAlerts = False
    WB.Range("B5:E11").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
'Now, clear filter
  On Error Resume Next
    WB.ShowAllData
  On Error GoTo 0
End Sub

The code sets the worksheet Filter Feature with VBA and the Range B5:E11.  AutoFilter Field:=3 searches for Criteria1:=”Cable” in the Product column of the dataset.

• Press F5 or click Run to run the code.

Example 3 – Delete Rows If the Cell Is Empty with Excel VBA

Use Excel VBA to delete rows if a cell is empty.

• Create a new module in the VBA window.
• Enter the following code in the module.

'Delete Rows if Cell is Empty
'Declaring Sub-procedure
Sub Delete_Rows_if_Cell_is_Empty()
'Select Range from where you want to delete rows containing empty cells
Range("B5:E11").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

• Press F5 or click Run to run the code.

Example 4 – Using Excel VBA to Delete Blank Rows

Use Excel VBA to delete blank rows automatically. The dataset was slightly modified.

Blank rows were added between multiple rows. To remove the blank rows:

• Create a new module in the VBA window.
• Enter the following code in the module.

'Deleting Blank Rows
'Declaring Sub-procedure
Sub Delete_Blank_Rows()
'Declaring Variables
Dim BlRw As Range
For Each BlRw In Range("B5:E15")
    If Application.WorksheetFunction.CountA(BlRw.EntireRow) = 0 Then
        BlRw.EntireRow.Delete
    End If
Next BlRw
End Sub

A For loop is used within B5:E15. An If statement checks whether the rows are empty with the help of the COUNTA function. The code deletes the empty rows.

• Press F5 or click Run to run the code.

Example 5 – Delete Rows Based on Specific Criteria Set by the User

You can set specific criteria to delete rows.

• Select the dataset.
• Go to the Insert tab and select Table.

• Click OK.

• A table is created.

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Deleting Rows Based on Specific Criteria Set by User
'Declaring Sub-procedure
Sub Delete_Rows_Based_on_Criteria_by_User()
'Declaring Variables
Dim LstObj As ListObject
Dim LgRows As Long
Dim FltrCriteria As Variant
  'Set the sheet name and Table as reference
  Set LstObj = ThisWorkbook.Worksheets("Specific Criteria Set by User").ListObjects(1)
  'Activate sheet that Table is on
  LstObj.Parent.Activate
  'Now, Clear if any filters exists
  LstObj.AutoFilter.ShowAllData
  'Enter the Filter criteria
  FltrCriteria = Application.InputBox(Prompt:="Enter the filter criteria for the Product column." _
                                    & vbNewLine & "Keep the box empty if you want to filter for blanks.", _
                                    Title:="Filter Criteria", _
                                    Type:=2)
  'Exit if user hits the Cancel button
  If FltrCriteria = False Then Exit Sub
  'Apply the Filter
  LstObj.Range.AutoFilter Field:=3, Criteria1:=FltrCriteria
  'Count Rows & display message
  On Error Resume Next
    LgRows = WorksheetFunction.Subtotal(103, LstObj.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible))
  On Error GoTo 0
    'The following part will Delete Filtered Rows
    Application.DisplayAlerts = False
      LstObj.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    'Now, Clear the Filter
    LstObj.AutoFilter.ShowAllData
End Sub

A Filter criterion is used in the code with the help of an InputBox. You need to enter the filter value in the box and the code will delete the rows that match the value.

• Press F5 or click Run to run the code.

Using Excel VBA to Delete Rows Based on Cells in Another Sheet

Delete rows based on cells in another sheet with the help of Excel VBA.

The “Dataset” sheet  is selected. Another sheet: “Delete Row On Another Sheet” will check it and delete rows that match the previous one. The ID column of the dataset was slightly changed.

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Deleting Rows Based on Cells in Other Sheet
'Declaring Sub-procedur
Sub Delete_Rows_Based_On_Cells_in_Other_Sheet()
'Declaring Variables
    Dim ws11 As Worksheet, ws22 As Worksheet
    Dim lasstRow As Long, i As Long, j As Long
    Dim DeleteRows As Range, cell As Range
    'Set "Sheet1" to the name of the sheet from where you want to delete rows from
    Set ws11 = Sheets("Based on Cells in Other Sheet")
    'Set "Sheet2" to the name of the sheet which you want to compare with
    Set ws22 = Sheets("Dataset")
    'Consider column B has data and finding the last row in column B
    lasstRow = ws11.Cells(ws11.Rows.Count, "B").End(xlUp).Row
    'My data started with row 5
    For i = 5 To lasstRow
    'Assuming column B has data and finding the last row in column B
        For j = 1 To ws22.Cells(ws22.Rows.Count, "B").End(xlUp).Row
        'Considering the cells which will be compared are in column B in both sheets
            If ws11.Cells(i, 2) = ws22.Cells(j, 2) Then
                If DeleteRows Is Nothing Then
                    Set DeleteRows = ws11.Rows(i)
                Else
                    Set DeleteRows = Union(DeleteRows, ws11.Rows(i))
                End If
                Exit For
            End If
        Next j
    Next i
    If Not DeleteRows Is Nothing Then DeleteRows.Delete
End Sub

In this code, the Dataset sheet is set to be compared to the Based on Cells in Other Sheet worksheet. Two For loops and two If statements compare the values of column B in the two sheets. If the value matches, the code will delete the rows.

• Press F5 or click Run to run the code.

Using Excel VBA to Delete Rows in Another Sheet

Delete rows in another sheet.

You need to know the Name of the Sheet and Range you want to remove. You are working in the“Dataset” sheet and want to delete rows in the “Delete Row On Another Sheet”. Here, B7:E10 was selected to delete rows.

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Deleting Row on Another Sheet
'Declaring Sub-procedure
Sub DeleteRows()
'Select the sheet name from where you want to delete rows and the Range
    ThisWorkbook.Sheets("Delete Row On Another Sheet").Range("B7:E10").Delete xlUp
End Sub

• Press F5 or click Run to run the code.

How to Remove Duplicate Rows in Excel VBA

The dataset was changed: rows were copied to create duplicates.

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Removing Duplicate Rows
'Declaring Sub-procedure
Sub Remove_Duplicate_Rows()
'Define the range from where you want to remove duplicates
Range("B5:E11").RemoveDuplicates Columns:=4
End Sub

In this code, B5:E11 is selected. Columns:=4 indicates the column index number: 4. The code will remove rows if it finds any duplicates within this range.

• Press F5 or click Run to run the code.

Excel VBA to Delete the Entire Row Based on a Cell Value

To remove an entire specific row:

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Declaring Sub-procedure
Sub Delete_Entire_Row_Based_on_Cell_Value()
'Declaring variables
Dim s_row As Long
Dim c_i As Long
'Provide the last row number
s_row = 12
For c_i = s_row To 1 Step -1
   If Cells(c_i, 4) = "Cable" Then
      Rows(c_i).Delete
   End If
Next
End Sub

In this code, a For loop and an If statement are used. The If statement checks for Cable in the Products column. 4 is the column index number.

• Press F5 or click Run to run the code.

How to Delete Rows and Shift the other rows Up with VBA in Excel

Here,B5: B11 is selected. The code will search for empty cells in Column B and delete them. The rows below will be shifted up.

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Deleting Row with VBA and Shift Up
'Declaring Sub-procedure
Sub Delete_Row_and_Shift_Up()
'Declaring Variables
    Dim Rw As Double
    Rw = Worksheets("Delete Row and Shift Up").Range("B11").End(xlUp).Row
    Worksheets("Delete Row and Shift Up").Range("B5:B" & Rw).SpecialCells _
    (xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
End Sub

Column B is selected as Range, B11 as the last row and B5 as the starting row. The code will delete rows if any cell within the range is empty and shift:=xlUp will move the remaining rows upwards.

• Press F5 or click Run to run the code.

Read More: Delete Row with VBA and Shift Up Cells in Excel

Using an Excel VBA Code to Delete Rows Based on Multiple Cell Values

I. Delete Rows If the Cell Value Is Not One of the specified Values

Column D was selected to search for specified values: Cable, Fridge, and TV. The code will delete rows other than these values.

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Delete Rows If Cell Value is Not One of Desired Values
'Declaring Sub-procedure
Sub Delete_Rows_If_Cell_Value_is_not_one_of_Desired_Values()
'Declaring variables
Dim Prt As Long
For Prt = Cells(Rows.Count, "D").End(xlUp).Row To 5 Step -1
    If Cells(Prt, "D").Value <> "Cable" And Cells(Prt, "D").Value <> "Fridge" And Cells(Prt, "D") <> "TV" Then
        Rows(Prt).EntireRow.Delete
    End If
Next
End Sub

A For loop and an If statement are used within column D. The code checks for Cable, Fridge, and TV and deletes rows that don’t contain these values.

• Press F5 or click Run to run the code.

Read More: How to Delete Row If Cell Contains Value Using Macro in Excel

II. Deleting Rows Based on Multiple Criteria

14107 in column B and TV in column D were set as the criteria. The code will delete rows that match both criteria.

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Delete Rows Based on multiple Criteria
'Declaring Sub-procedure
Sub Delete_Rows_based_on_Multiple_Criteria()
'Declaring variables
    Dim LastRow As Long
    Dim p As Long
    'Get the last row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    'Start from the last row and loop upwards
    For p = LastRow To 5 Step -1
        'Check if both conditions are met
        If Cells(p, "B") = "14107" And Cells(p, "D") = "TV" Then
            'Delete the entire row
            Rows(p).Delete
        End If
    Next p
End Sub

The If statement searches for 14107 in column B and TV in column D. With the help of For loop, every cell within the range is checked. The code will delete rows that meet both conditions.

• Press F5 or click Run to run the code.

III. Delete Rows That are Empty in Column B and Not-Empty in Column D

To delete rows that are empty in column B and not empty in column D, setting a value from column D: (TV) as not empty cell value:

• Insert a new module in the VBA window.
• Enter the following code in the module.

'Deleting Rows which is Empty in Column B and Not-Empty in Column D
'Declaring Sub-procedure
Sub Delete_Rows_based_on_Empty_Non_Empty_Cell()
'Declaring variables
    Dim LastRow As Long
    Dim p As Long
    'Get the last row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    'Start from the last row and loop upwards
    For p = LastRow To 5 Step -1
        'Check if both conditions are met
        If Cells(p, "B") = "" And Cells(p, "D") = "TV" Then
            'Delete the entire row
            Rows(p).Delete
        End If
    Next p
End Sub

The If statement searches for Blank cells in column B and TV in column D. With the help of the For loop, every cell within the range is checked. The code deletes rows that meet both conditions.

• Press F5 or click Run to run the code.

Download Practice Workbook

Download the workbook.

Related Articles

• How to Delete Selected Rows with Excel VBA
• Excel VBA to Delete Table Row