VBA Macro to Delete Rows Based on Cell Values

Bottom Line: Learn how to use VBA macros to delete rows based on cell values or conditions. Includes video tutorial and sample code.

Skill Level: Intermediate

Video Tutorial

Download the Excel File

Here is the file I use in the video above that contains the VBA macro code examples.

Using a Macro to Delete Rows Based on Cell Values

Does your data preparation process include deleting the same rows based on a condition? If so, you can use a macro to instantly delete any rows that have a particular value, date, or even blank cells.

The overall process is two simple steps:

1. The first step is to filter the rows based on filter criteria for the values to be deleted.
2. Then the macro deletes the visible cells in the range.

The Process Explained

Below is an image of a data set that has some blank cells in column E (Product). You can see one of those blank cells is E6.

To remove the rows that have blank cells like this one, the macro first applies a filter to the product column.

Next, the macro simply deletes all of the visible rows that have been left by the filter. It uses the SpecialCells method to create a reference to the visible cells.

This is the same as using the Go To Special menu (keyboard shortcut Alt+;) to select blanks. Checkout my article and video on how to copy & paste visible cells to learn more.

Finally, the macro can also clear the filters so that you are left viewing the entire data range, minus the rows you've deleted.

It's a simple 2 or 3 step macro that will save time from doing this process manually.

Important Note: I added a step to clear all filters in the range or Table at the beginning of the macro. This ensures that there are no filters applied to other columns that could cause additional rows to be filtered out.

A big thanks to Hoang for pointing this out on the YouTube video!

The VBA Macro Code

The VBA code below can be copy/pasted to the VB Editor. The code is also included in the Excel file in the Download section above.

Sub Delete_Rows_Based_On_Value()
'Apply a filter to a Range and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/


Dim ws As Worksheet

  'Set reference to the sheet in the workbook.
  Set ws = ThisWorkbook.Worksheets("Regular Range")
  ws.Activate 'not required but allows user to view sheet if warning message appears
  
  'Clear any existing filters
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

  '1. Apply Filter
  ws.Range("B3:G1000").AutoFilter Field:=4, Criteria1:=""
  
  '2. Delete Rows
  Application.DisplayAlerts = False
    ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
  
  '3. Clear Filter
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

End Sub

You will just need to update the Worksheet and Range references for your specific file.

Avoiding the Pop-up Warning

When you run the macro you will receive a pop-up warning message that says “Delete entire sheet row?”

If you want to run the macro without having that pop-up box interrupting, you can simply remove the apostrophes before the two lines of code that begin with Application.DisplayAlerts.

So that portion of the macro now looks like this:

  '2. Delete Rows
  Application.DisplayAlerts = False
    ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True

The Application.DisplayAlerts property is a toggle to turn warning alerts on/off. Setting it to False will turn off the alerts. These are alerts you might see from Excel when deleting ranges, deleting sheets, closing without saving, etc.

Applying the Macro to Tables

If your data is in an Excel Table instead of just a range of cells, you can still delete rows based on cell contents using a macro. The code is almost the same, but tweaked slightly so that it applies to Tables. Here is the code you would use for a Table.

Sub Delete_Rows_Based_On_Value_Table()
'Apply a filter to a Table and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/

Dim lo As ListObject

  'Set reference to the sheet and Table.
  Set lo = Sheet3.ListObjects(1)
  ws.Activate
  
  'Clear any existing filters
  lo.AutoFilter.ShowAllData
  
  '1. Apply Filter
  lo.Range.AutoFilter Field:=4, Criteria1:="Product 2"
  
  '2. Delete Rows
  Application.DisplayAlerts = False
    lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True

  '3. Clear Filter
  lo.AutoFilter.ShowAllData

End Sub

Additional Macros

I've also included a few additional macros to help customize the process further.

Custom Warning Message

You can create a custom message box before deleting rows, instead of the default Excel warning to delete rows. The macro below also tells you the number of rows it is going to delete, and asks if you want to proceed.

Sub Delete_Rows_Based_On_Value_Table_Message()
'Display Yes/No message prompt before deleting rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/

Dim lo As ListObject
Dim lRows As Long
Dim vbAnswer As VbMsgBoxResult

  'Set reference to the sheet and Table.
  Set lo = Sheet6.ListObjects(1)
  lo.Parent.Activate 'Activate sheet that Table is on.
  
  'Clear any existing filters
  lo.AutoFilter.ShowAllData
  
  '1. Apply Filter
  lo.Range.AutoFilter Field:=4, Criteria1:="Product 2"
  
  'Count Rows & display message
  On Error Resume Next
    lRows = WorksheetFunction.Subtotal(103, lo.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible))
  On Error GoTo 0
  
  vbAnswer = MsgBox(lRows & " Rows will be deleted.  Do you want to continue?", vbYesNo, "Delete Rows Macro")
  
  If vbAnswer = vbYes Then
    
    'Delete Rows
    Application.DisplayAlerts = False
      lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
  
    'Clear Filter
    lo.AutoFilter.ShowAllData
    
  End If

End Sub

This is what the custom pop-up box looks like:

This pop-up just serves as a check before deleting rows. It's a great way to double check everything looks good before deleting. It also prevents you or your users from accidentally running the macro.

Checkout my video on how to add a Yes/No message box before the macro runs for details on this code. It's part of my 4 part video series on the Personal Macro Workbook.

Deleting Rows Based on Multiple Criteria

You can also delete rows with a macro using more than one criteria. In the video above, I filter for rows that have both a blank Product field and a date before 1/1/2015.

Sub Delete_Rows_Based_On_Multiple_Values()
'Apply a filter to a Table and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/

Dim lo As ListObject

  'Set reference to the sheet and Table.
  Set lo = Sheet5.ListObjects(1)
  lo.Parent.Activate 'Activate sheet that Table is on.
  
  'Clear any existing filters
  lo.AutoFilter.ShowAllData
  
  '1. Apply Filter - Blanks in Product for before 2015 only
  lo.Range.AutoFilter Field:=4, Criteria1:=""
  lo.Range.AutoFilter Field:=1, Criteria1:="<1/1/2015"
  
  '2. Delete Rows
  Application.DisplayAlerts = False
    lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True

  '3. Clear Filter
  lo.AutoFilter.ShowAllData

End Sub

This allows you to delete rows based on values in multiple columns. The filtering essentially uses AND logic between the columns and all conditions must be met.

If you want to use OR logic where any of the conditions are met, you can run multiple macros, or create a formula in a helper column with the logic.

Depending on the logic, it might be easiest to just create two separate macros. You can put the code all in one macro, or create another macro to call each macro. Here's an example of what that macro might look like.

Sub Delete_Rows()

  Call Delete_Blank_Rows
  Call Delete_Before_2015

End Sub

This method also allows you to run each macro individually.

Delete Rows Based on Criteria Specified by User

Daniel and Bob asked a great question in the comments below. They want the user of the file to be able to specify the filter criteria, instead of having it hard-coded in the macro.

We can use the Application.InputBox method to ask the user to input the criteria.

The InputBox method creates a pop-up message box that allows the user to type in the text box. The value in the text box is passed back to the macro and stored in a variable.

Here is the VBA code. I also added it to the sample file in the downloads section above.

Sub Delete_Rows_User_Input()
'Display Yes/No message prompt before deleting rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/

Dim lo As ListObject
Dim lRows As Long
Dim vbAnswer As VbMsgBoxResult
Dim sCriteria As Variant

  'Set reference to the sheet and Table.
  Set lo = Sheet9.ListObjects(1)
  lo.Parent.Activate 'Activate sheet that Table is on.
  
  'Clear any existing filters
  lo.AutoFilter.ShowAllData
  
  'Ask user for input
  sCriteria = Application.InputBox(Prompt:="Please enter the filter criteria for the Product column." _
                                    & vbNewLine & "Leave the box empty to filter for blanks.", _
                                    Title:="Filter Criteria", _
                                    Type:=2)
  
  'Exit if user presses Cancel button
  If sCriteria = False Then Exit Sub
  
  '1. Apply Filter
  lo.Range.AutoFilter Field:=4, Criteria1:=sCriteria
  
  'Count Rows & display message
  On Error Resume Next
    lRows = WorksheetFunction.Subtotal(103, lo.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible))
  On Error GoTo 0
  
  vbAnswer = MsgBox(lRows & " Rows will be deleted.  Do you want to continue?", vbYesNo, "Delete Rows Macro")
  
  If vbAnswer = vbYes Then
    
    'Delete Rows
    Application.DisplayAlerts = False
      lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
  
    'Clear Filter
    lo.AutoFilter.ShowAllData
    
  End If

End Sub

Pre-fill the User's Name in the Input Box

Daniel also had a question about setting the filter criteria to the current user's name. The Application.UserName property actually returns this value as long as the user has set it properly in Office/Excel.

We can use this as the Default parameter value in the input box.

  sCriteria = Application.InputBox(Prompt:="Please enter the filter criteria for the Product column." _
                                    & vbNewLine & "Leave the box empty to filter for blanks.", _
                                    Title:="Filter Criteria", _
                                    Default:=Application.UserName, _
                                    Type:=2)

The user might still need to change the name if it does not match the value in the worksheet column you are filtering, but you can also get that synced up with the user to save them a lot of time.

Related Topics

Check out my posts on these similar topics as well.

• The Ultimate Guide to Filters with VBA Macros
• How to Copy and Paste Visible Cells Only
• 3 Ways to Delete Entire Blank Rows
• How to Create Your Personal Macro Workbook

Conclusion

Macros like this can be especially helpful if you are looking to clean up data and get rid of entries that you know you don't need–maybe because they are too old or they are only partially complete.

There are a lot of ways to accomplish this task. Another common approach is to loop through cells and check their values individually.

However, I typically use this approach because it can be a lot faster to use the built in filters in Excel. It also allows us to preview the range before we delete it.

What will you use these macros for? Please leave a comment below with any suggestions and questions. Thank you! 🙂