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

Download the Excel File

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

VBA Macro To Delete Rows Based On Cell Value.xlsm (490.7 KB)

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.

Delete blank cells using a macro

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.

Rows with blank cells deleted

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

New data set with blank rows 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:

Delete Rows Macro Custom Message

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.

Input Box to Prompt User for Filter Criteria for Delete Rows Macro

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.

Input Box to Prompt Default to User Name

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.

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! 🙂

21 comments

Your email address will not be published. Required fields are marked *

  • please am a novice in VBA i will like to modify your VBA code for multiple criteria to filter my data but am having my first column A as date and time in this format 1/1/2015 0:00 so i want to delete each row except between 9:00 and 18:00 daily for every day in a month as each sheet has data for a complete month. please i need your assistance. thanks.

  • Dear Jon, Wonderful, as usual.
    Please correct the code in the Excel Table first example above:
    ws.Activate

    the variable ws has not been declared & hence the code is not performing.

    All that I have learnt in VBA is because of you largely.

  • Hi Jon

    I am trying to delete a number of rows from a worksheet as per your first example. I would like to pick up the “Criteria” from a cell on another worksheet in the same workbook.

    I have tried the following:

    Dim Wk as String
    Set Wk = ThisWorkbook.Worksheets(“Design”).Range(“K2”)
    .
    .
    .
    .
    ws.Range(“A2:D1000”).AutoFilter Field:=3, Criteria1:=Wk

    but I get a compile error at the Set WK line of code!

    Where am I going wrong?
    Your help would be much appreciated.

  • Hi Jon,

    this is super helpful.
    When I modify the criteria it all works, but when there are no rows that match the criteria (I used the code for multiple criteria) it somehow gets stuck, shows no rows and does not show the remaining data again.
    Is there anything I am doing wrong (I used exactly your code and it is working fine if there are rows that match the critera) or should something else be applied to the code?

    Thanks a lot
    Christina

  • I am great fan of your easy to understand codes with very simple illustrations to demonstrate what and how the code works. I really appreciate the way you explain and write the code so that non professional or rather beginner can easily understand and modify the code according to his requirements. Thanks for your superb tutorials keep it up.

  • Hi Jon,

    Thanks for this! Is there any way to make your filter look up the values to use to filter? I have a list of things that I need to filter by which I usually do manually using an advanced filter – any way to include this in the macro?

    Thanks
    Bec

  • Jon, I love your videos and your instructions! Do you plan on authoring any books?

    In order to avoid errors if the range or table has been saved with filters applied,
    I add this line of code before I filter with my criteria:

    If worksheets(“name”).filtermode = true then worksheets(“name”).showalldata

  • Thanks again Jon,

    Another for me very useful set of macros.

    Together with the “delete blank rows” macro, this comes in very handy.

  • Jon, your macros are extremely helpful!

    When I use the custom warning message macro, it breaks counting the rows, with the error message: “424: object required”.

    I’m using your exact code:

    lRows = WorksheetFunction.Subtotal(103, lo.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible))

    Does it make a difference that I’m sorting a csv file as opposed to excel file?

    Thanks

    • Hi Gary,
      The file should definitely be saved as an xlsm file. This code is also for an Excel Table, so the data will need to be in an Excel Table. Or you can modify the code for regular ranges, which is in the first macro.

      I hope that helps.

  • Hi Jon – Thanks for another relevant topic and your perfectly executed explanation (and accompanying visuals!) I was wondering if you would consider adding vba code asking the user their filtering/deleting criteria, and then applying their response to the data. This method eliminates the need to write more than one set of macro instructions.
    Thanks again for sharing your Excel expertise!!

    Bob

    • Hi Bob,
      Great question! I just added a new section in the article above for this feature. It uses the Application.InputBox method to prompt the user to specify the filter criteria.

      The sample file has also been updated with the new macro.

      I hope that helps. Thanks! 🙂

  • Hi John, as always very informative. Brand new to VBA so doubly appreciate your granular and detailed approach. It “appears” that the range is hard coded for the filter (B3:G1000).

    Is it difficult to determine this range on the fly? .. most situations I encounter, the range is dynamic from one day to the next.

    I would like to begin capturing snippets for reuse and identifying and documenting the multitude of ways to determine the range to manipulate is far and away at the top of my list. Thanks for any direction..

  • Hey Jon,

    How would you modify these macros so they take input for the criteria?

    Is there a way to say Blank as an input and how would it be sent to the Criteria part of the AutoFilter?

    This would allow anyone to use it for whatever data they have.

    I guess getting multiple inputs would be a little bit harder to implement but how would we go about it? I currently have a similar macro just for filtering my data based on multiple criteria so I can see my workload for the week but if I want to pass it to someone else I have to create a copy of the code and change my name to theirs.

    • Hi Daniel,

      Great question! I added a section to the article above that answers this. I added a new macro that uses the InputBox method to prompt the user to input the filter criteria.

      I also added a section that will return the user’s name to the InputBox as a default value, using the Application.UserName property. This will work well if the user has set their name in Office/Excel, and it matches the value in your spreadsheet. If it doesn’t match then they can retype it in the InputBox. Or you can change Office or the value in the file so they match, and this will save the user time with typing their name.

      The sample file also contains the new macro.

      I hope that helps. Thanks again!

  • Very interesting, wouldn’t have thought to do it that way.
    My usual way would be to use a For Next loop to check each row individually.
    You’re way is much more elegant and considerably faster I would presume

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly