VBA to Delete Rows Based On Cell Value

If you need to delete rows in Excel based on a cell value, such as a date or a text string, you can easily accomplish this task using VBA.

In this tutorial, I will show you seven examples of how to delete rows in Excel based on a cell value using VBA.

Note: It is crucial to perform a data backup before using any of the provided example codes in this tutorial, as they directly modify your data, and you cannot undo the changes.

You can adjust the example codes according to your specific requirements and test them on a copy of your data before applying them to your actual data.

VBA to Delete Rows Based on Cell Values (Exact Match)

Suppose you have the dataset below on Sheet1:

You can delete the rows with ‘Laptops’ in the category column using the code below:

Sub DeleteRowsBasedOnExactMatch()

  Dim ws As Worksheet
  Dim rng As Range
  Dim cell As Range
  Dim i As Long

  Set ws = ThisWorkbook.Sheets("Sheet1")
  Set rng = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

  For i = rng.Rows.Count To 2 Step -1
      If rng.Cells(i, 1).Value = "Laptops" Then
          rng.Cells(i, 1).EntireRow.Delete
      End If
  Next i

End Sub

When you run the code, it deletes all the rows containing ‘Laptops’ in column B:

Explanation of the Code

Here’s an explanation of the less apparent statements in the code:

1. ‘Set rng = ws.Range(“B2:B” & ws.Cells(ws.Rows.Count, “B”).End(xlUp).Row)‘ : This line sets the variable ‘rng’ to reference cells in column B (from B2 to the last non-empty cell in column B).
2. ‘For i = rng.Rows.Count To 1 Step -1‘: This statement initiates a loop starting from the last row in the range (‘rng.Rows.Count’) and moving backward (decreasing ‘i’ by 1 in each iteration). Using a reverse loop prevents issues with row shifting that can occur when deleting rows in a forward loop.

Note: When you use the statement ‘cell.EntireRow.Delete’ to delete a row, the rows below it fill the space. If you use a forward loop to check for values in cells, it might miss some rows you want to delete because the rows have shifted upwards.

3. ‘If rng.Cells(i, 1).Value = “Laptops” Then‘: This line checks if column B’s current cell’s value is “Laptops.”
4. ‘rng.Cells(i, 1).EntireRow.Delete‘: If the condition in the previous line is true, this line deletes the entire row of the current cell.

Also read: Count Rows using VBA in Excel

VBA to Delete Rows Based on Cell Values (Partial Match)

Assuming you have the below dataset on Sheet1:

Please take note of the variations in the naming of the ‘Tablets’ category in the cell range B5:B7 due to inconsistency in data entry.

You can delete all rows that contain  various names for the ‘Tablets’ category in column B by using the VBA code below:

Sub DeleteRowsPartialMatch()

Dim ws As Worksheet
Dim rng As Range
Dim i As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)

For i = rng.Rows.Count To 2 Step -1
  If InStr(1, rng.Cells(i, 1).Value, "Ta") > 0 Then
      rng.Cells(i, 1).EntireRow.Delete
  End If
Next i

End Sub

When you execute the code, it deletes all the rows containing the substring ‘Tab’ in column B:

Explanation of the Code

The VBA code loops through each row in column B of the dataset on Sheet1, checks if the cell value contains the substring ‘Ta,’ and deletes the entire row if a match is found. The loop runs reversely to avoid issues with shifting rows as they are deleted, as explained in Example #1.

The code utilizes the ‘InStr’ function to return the position of the first occurrence of ‘Ta’ in the string. If the position is greater than 0, there is a partial match.

Also read: Delete Blank Rows Using VBA

VBA to Delete Rows with Values Less Than a Specific Number

Suppose you have the below dataset on Sheet1:

You can delete all rows with values less than 300 in column D using the code below:

Sub DeleteRowsNumericThreshold()

Dim ws As Worksheet
Dim rng As Range
Dim i As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("D2:D" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row)

For i = rng.Rows.Count To 2 Step -1
If IsNumeric(rng.Cells(i, 1).Value) _
And rng.Cells(i, 1).Value < 300 Then
    rng.Cells(i, 1).EntireRow.Delete
End If
Next i

End Sub

When you run the subroutine, it deletes all the rows with values less than 300 in column D:

Explanation of the Code

The VBA code goes through each row in column D of the dataset on Sheet1 and checks if the value is numeric and less than 300, and deletes the entire row if both conditions are satisfied.

The loop runs in reverse order to avoid issues with shifting rows as they are deleted, as explained in Example #1.

To suit your requirements, you can modify the code to delete rows with numeric values greater than, equal to, or not equal to a particular threshold.

Also read: VBA to Remove Duplicates in Excel

VBA to Delete Rows Based on Dates

Suppose you have the following dataset on Sheet1:

You can delete all the rows containing items acquired before January 1, 2000, using the VBA code below:

Sub DeleteRowsBasedOnDate()

Dim ws As Worksheet
Dim rng As Range
Dim i As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("D2:D" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row)

For i = rng.Rows.Count To 2 Step -1
If IsDate(rng.Cells(i, 1).Value) And rng.Cells(i, 1).Value _
< #1/1/2000# Then
    rng.Cells(i, 1).EntireRow.Delete
End If
Next i

End Sub

When you execute the code, it removes all rows containing items acquired before January 1, 2000:

Explanation of the Code

The VBA code checks each cell in column D of the dataset on Sheet1. If the value in the cell is a valid date and earlier than January 1, 2000, the entire row is deleted.

The loop runs in reverse order to avoid issues with shifting rows as they are deleted, as explained in Example #1.

To fit your needs, you can modify the code to delete rows based on dates greater than, equal to, or not equal to a specific date.

Also read: VBA to Freeze Top Row in Excel

VBA to Delete Rows with Blank Cells in One Column

Assume you have the dataset below on Sheet1:

Notice the blank cells C2 and C8.

You can delete all the rows with blank cells in column C using the VBA code below:

Sub DeleteRowsWithBlankCells()

Dim ws As Worksheet
Dim rng As Range
Dim BlankCellsColumn As Long
Dim i As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.UsedRange
BlankCellsColumn = 3

For i = rng.Rows.Count To 2 Step -1
    If rng.Cells(i, BlankCellsColumn) = "" Then
        rng.Cells(i, BlankCellsColumn).EntireRow.Delete
    End If
Next i

End Sub

When you run the subroutine, it deletes the rows with blank cells in column C:

Explanation of the Code

The VBA code scans column 3 of the used range in reverse order. For each cell in the column, it checks whether the cell is empty. If the condition is true, the code deletes the entire row.

The reverse order is used to prevent any issues with row deletion that might affect the subsequent iterations of the loop, as explained in Example #1.

Also read: VBA to Hide Rows Based On Cell Values

VBA to Delete Visible Rows Except for Header Row

Suppose you have the following dataset on Sheet1:

You can use the VBA code below to filter column C for ‘Dell’ and delete visible rows except for the header row:

Sub FilterDeleteRowsBasedOnValue()

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")
On Error Resume Next

ws.ShowAllData

On Error GoTo 0

ws.UsedRange.AutoFilter Field:=3, Criteria1:="Dell"

Application.DisplayAlerts = False

ws.Rows("2:" & ws.Rows.Count).SpecialCells(xlCellTypeVisible).Delete

ws.AutoFilterMode = False

Application.DisplayAlerts = True

On Error Resume Next
ws.ShowAllData
On Error GoTo 0

End Sub

When you execute the code, it deletes rows with ‘Dell’ in column C:

Explanation of the Code

The code filters the rows in the third column based on the value ‘Dell.’ After that, it deletes all visible cells except the header row.

Finally, it clears the filter to show the whole dataset except for the deleted rows.

Also read: Unhide All Rows in Excel with VBA

VBA to Apply Filters to Two Columns and Delete Visible Rows

Suppose you have the below dataset on Sheet1:

You can use the VBA code below to filter column B for ‘Tablets’ and column D for a value less than $300 and delete visible rows except the header row:

Sub FilterDeleteRowsBasedOnTwoColumns()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

On Error Resume Next

ws.ShowAllData

On Error GoTo 0

ws.UsedRange.AutoFilter Field:=2, Criteria1:="Tablets"
ws.UsedRange.AutoFilter Field:=4, Criteria1:="<300"

Application.DisplayAlerts = False

On Error Resume Next

ws.Rows("2:" & ws.Rows.Count).SpecialCells(xlCellTypeVisible).Delete

On Error GoTo 0

ws.AutoFilterMode = False

Application.DisplayAlerts = True

On Error Resume Next
ws.ShowAllData
On Error GoTo 0

End Sub

When you run the code, it deletes the row in the dataset that has ‘Tablets’ in column B and a value less than $300 in column D:

Explanation of the Code

The subroutine filters the rows based on the value ‘Tablets’ in column B and a value less than 300 in column D, deletes the visible rows minus the header row, and then clears all filters so that you are left viewing the entire dataset minus the rows you’ve deleted.

In this tutorial, I showed you some examples of using VBA to delete rows in Excel based on cell values. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• How to Delete a Sheet in Excel Using VBA
• How to Rearrange Rows In Excel
• VBA to Find Last Row in Excel
• Autofill to Last Row Using VBA in Excel
• VBA to Insert Row in Excel