How to Filter Data and Delete Rows with Excel VBA (5 Examples)

In Microsoft Excel, you can use the VBA code to delete rows and columns very efficiently. To delete rows or columns in Excel at large numbers, we use the VBA codes. It is one of the crucial tasks. Another useful feature of  Excel is filtered data. We can perform a lot of operations with these. Now, you may face a situation where you have to use the VBA to delete rows in Excel that have already been filtered. In this tutorial, you will learn to filter data and delete rows in Excel using the VBA macro. It will be on point with suitable examples and proper illustrations. So, read the whole article to develop your Excel knowledge.

Download Practice Workbook

5 Useful VBA Macros to Filter Data and Delete Rows in Excel

In the following sections, I will show you five useful VBA macros to filter and delete rows in Excel that you can implement into your worksheet. I recommend you learn and apply all these macros to your worksheet. It will definitely increase your Excel knowledge in the future.

1. Filter Data and Delete Rows That Are Visible in Excel

In this section, I will show you how to filter data and delete an entire row based on criteria in Excel.

Take a look at the following screenshot:

Here, you can see a sales dataset list. Now, we will filter data from the dataset based on the product “TV”. After that, we will delete the entire row of the product “TV”.

Now, follow these steps to execute the VBA macro.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, click on Insert>Module.

• After that, type the following code:

Sub filter_delete_visible_rows()

Dim rng As Range

Set rng = Selection

rng.AutoFilter Field:=3, Criteria1:="TV"

rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.AutoFilterMode = False

End Sub

• Then, save the file.
• Now, select the range of cells B4:F16.

• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select filter_delete_visible_rows.
• Then, click on Run.

Now, look closely. There is no data on the product “TV”. So, we successfully filter the data and delete the rows using the VBA in Excel.

Read More: VBA to Delete Entire Row based on Cell Value in Excel (3 Methods)

2. Filter Data and Delete Rows That Are Hidden in Excel

In this method, I will show you to delete rows of a filtered list using VBA code. We will perform this based on two criteria. The first one is to delete entire rows that have the product “Fridge”. And the second one is to delete the rows that include the region “North”.

Now, I hide those rows that match our given criteria. That means those rows are hidden. To hide this, click on the filter data icon and try to match the criteria.

Now, follow these steps.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type the following code:

Option Explicit

Sub delete_hidden_rows()

Dim union_of_rows As Range

Dim r As Range

Dim Rng As Range

Set Rng = Selection

Rng.AutoFilter Field:=2, Criteria1:="<>North"

Rng.AutoFilter Field:=3, Criteria1:="<>Fridge"

For Each r In Rng.Rows

  If r.Hidden Then

   If Not union_of_rows Is Nothing Then

    Set union_of_rows = Union(union_of_rows, r)

   Else

    Set union_of_rows = r

   End If

  End If

Next

union_of_rows.Delete

ActiveSheet.AutoFilterMode = False

End Sub

• Then, save the file.
• Now, select the range of cells.

• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select delete_hidden_rows.
• Then, click on Run.

After that, the VBA code will delete all the filtered data from the hidden rows in Excel.

Read More: How to Delete Hidden Rows in Excel VBA (A Detailed Analysis)

3. Delete Rows Based on a Cell Value

Now, we already showed this previously if you noticed. We actually deleted rows based on the cell values previously. Here, we are giving you another example to clear those rows based on a cell value.

Take a look at the following screenshot:

Here, you can see, we have some missing data in the dataset. Now, we will delete the rows from this filtered list using the VBA code based on the cell values.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, click on Insert>Module.

• After that, type the following code:

Sub filter_delete_rows_based_cell()

Dim current_sheet As Worksheet

  Set current_sheet = ThisWorkbook.Worksheets("Cell Value")

  current_sheet.Activate

 current_sheet.Range("B5:F16").AutoFilter Field:=4, Criteria1:=""

  Application.DisplayAlerts = False

current_sheet.Range("B5:F16").SpecialCells(xlCellTypeVisible).Delete

  Application.DisplayAlerts = True

End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select filter_delete_rows_based_cell.
• Then, click on Run.

As you can see, our VBA code will delete the rows from the filtered list that have empty cell values in Excel.

Read More: VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)

Similar Readings:

• How to Delete Unused Rows in Excel (8 Easy Ways)
• Delete Alternate Rows in Excel (5 Quick Ways)
• How to Delete Hidden Rows in Excel (3 Methods)
• Macro to Delete Row in Excel If Cell is Blank
• Excel VBA Code to Delete Rows Based on Multiple Cell Value (3 Criteria)

4. Filter Data and Delete Rows Using Multiple Criteria in VBA

Now, I haven’t shown you the VBA codes that include multiple criteria in the same column. Basically, you can include multiple criteria in a column to delete rows from a filtered list using VBA code in Excel.

Take a look at the following screenshot:

Here, we will use the VBA to filter data the rows based on the product “TV” and “Mobile”. After that, we will delete those entire rows based on them.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, click on Insert>Module.

• After that, type the following code:

Sub filter_delete_multiple_criteria()

Dim rng As Range

Set rng = Selection

rng.AutoFilter Field:=3, Criteria1:="TV", Operator:=xlOr, Criteria2:="Mobile"

rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.AutoFilterMode = False

End Sub

• Then, save the file.
• Now, select the range of cells B4:F16.

• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select filter_delete_multiple_criteria.
• Then, click on Run.

Now, look closely. There is no data on the product “TV” and “Mobile”. So, we successfully delete the filtered rows using the VBA in Excel.

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)

5. Filter Data and Delete Rows Based on Condition Specified by User

Now, you can filter data and delete rows based on the conditions specified by the user using the VBA. It is almost similar to the previous one.

Take a look at the screenshot:

Here, we will filter data from the dataset based on the Region “South” and after that, will delete those rows using the VBA.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, click on Insert>Module.

• After that, type the following code:

Sub filter_delete_rows_userInput()

Dim rng As Range

Dim answer As Variant

Set rng = Sheets("User Input").Range("B4:F16")

answer = Application.InputBox(Prompt:="Please enter the filter criteria for the Region column." _

                                    & vbNewLine & "Leave the box empty to filter for blanks.", _

                                    Title:="Filter and Delete Rows", _

                                    Type:=2)

 If answer = False Then Exit Sub

rng.AutoFilter Field:=2, Criteria1:=answer

rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.AutoFilterMode = False

End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select filter_delete_rows_userInput.
• Then, click on Run.

• Now, enter the value by which you want to filter data from the dataset. We are giving here “South”.
• After that, click on OK.

As you can see from the screenshot, we have no rows in the dataset for the region “South”. Here, I used the VBA to filter data and delete the filtered rows from the dataset in Excel.

Related Content: How to Delete Multiple Rows in Excel with Condition (3 Ways)

Delete Filtered Rows but Not Header in Excel Using VBA

Now, we already showed some VBA codes earlier. On those codes, you can find this solution. Our VBA code didn’t delete the header rows. Why is that? Take a look at the following code:

rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

When we filtered the dataset by the criteria, we have some visible rows. Now, that includes the header rows also. Now, if we delete the entire rows, our header rows will also be deleted. That’s why we used the Offset method. We gave Offset(1,0) to start deleting after the header row. That’s why our VBA code didn’t consider the header row for deleting in Excel. After executing the code, all the rows below the header will be deleted.

Additional VBA Codes to Delete Rows in Excel

In the following sections, I am going to provide you with some basic VBA code snippets to delete rows in Excel quickly. Apply these VBA codes to your worksheet.

1. Delete Entire Rows

To delete an entire row, use the following code:

Rows(1).Delete

It will delete row number 1 totally.

Now, if you are in a cell and you want to delete the entire row, use the following code:

Range("B4").EntireRow.Delete

It will delete the entire row “B”.

Read More: How to Use VBA to Delete Empty Rows in Excel

2. Delete Multiple Rows

To delete multiple rows by the row number, use the following VBA code in Excel:

Rows("2:5").Delete

It will delete rows from 2 to 5.

To delete rows based on a range:

Range("B4:B10").EntireRow.Delete

3. Delete Blank Rows in Excel

If you have multiple blank rows in your dataset, delete them by using the following VBA code;

Sub belete_blank_rows()

Dim cell_value As Range

For Each cell_value In Range("B4:D10")

    If Application.WorksheetFunction.CountA(cell_value.EntireRow) = 0 Then

        cell_value.EntireRow.Delete

    End If

Next cell_value

End Sub

This VBA code will check the rows in the range B4:D10 that are entirely blank and delete them.

Read More: Delete Blank Rows in Excel (10 Handy Methods)

4. VBA to Delete Row If Cell is Blank

If you have blank cells in a given range and want to delete that, use the following VBA code:

Range("B4:B20").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

5. Delete Row Based on a Cell Value

We already showed an example earlier. But that was for the filtered list. You can directly delete rows based on cell value.

Sub delete_rows_based_on_values()

Dim cell As Range

For Each cell In Range("C5:C16")

    If cell.Value = "North" Then

        cell.EntireRow.Delete

    End If

Next cell

End Sub

It will delete the rows where the cell value is “North”.

6. Remove Duplicate Rows Using VBA

The following code will delete all the duplicate rows in Excel:

Range("B4:D10").RemoveDuplicates Columns:=2

Columns:=2: It means the VBA will match both the first two columns of data when evaluating if rows are duplicates. When both columns have duplicate values, a duplicate is only found then.

If we fixed this to 1, it only checks the first row for duplicate values.

Read More: How to Delete Duplicate Rows in Excel with VBA (8 Effective Ways)

7. Delete Rows in a Range

To delete rows in a range:

Range("B4:B10").EntireRow.Delete

8. Delete Selected Rows in Excel

To delete selected rows, use the following code:

Selection.EntireRow.Delete

9. VBA to Delete Last Row

To delete the last used row in column B:

Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete

To delete the last used row in column C, change 2 to 3:

Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete

💬 Things To Remember

✎ Make sure to use the Offset method to delete only the data of rows, not the headers.

✎ In the “Field:=” enter the column number of your dataset to filter data and delete rows in Excel.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the VBA codes to filter data in Excel and delete rows. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• Excel VBA to Delete Rows with Specific Data (9 Examples)
• Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)
• How to Delete Multiple Rows in Excel at Once (5 Methods)
• Delete Hidden Rows in Excel VBA (A Detailed Analysis)
• How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)