How to Filter and Delete Rows with VBA in Excel (2 Methods)

When we apply the Excel Filter feature to our dataset, some rows get hidden while others are visible. In such a case, deleting the rows we wish may not be so simple. In this article, we’ll show you the easy ways to Filter and Delete Rows with VBA in Excel.


Download Practice Workbook

To practice by yourself, download the following workbook.


Dataset Introduction

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company. Here, we have 2 types of products: Cable and AC. We’ll apply the Filter to the products and then delete the rows we don’t want anymore.

Filter and Delete Rows with VBA in Excel


2 Methods to Filter and Delete Rows with VBA in Excel

1. Filter the Visible Rows and Delete with VBA in Excel

If we apply the filter, we’ll get some visible rows and some hidden rows. In this method, we’ll show you the process to Delete the Visible Rows. When we apply the Filter feature with VBA, we can set criteria. It can be a single criterion or multiple depending on our requirements. First, we’ll show how to set a single criterion and delete the visible rows. In the below dataset, we’ll use the product AC as our single criterion. So, only the rows with the product AC will be visible.

Filter the Visible Rows and Delete with VBA in Excel


1.1 Single Criterion

Now, follow the steps below to know how to apply the filter for a single criterion with VBA and then delete the visible rows.

STEPS:

  • Firstly, under the Developer tab, select Visual Basic.

Filter the Visible Rows and Delete with VBA in Excel

  • Then, a new window will pop out, and there, select Module under the Insert tab.

Filter the Visible Rows and Delete with VBA in Excel

  • Consequently, another window will pop out.
  • After that, copy the code given below and paste it into the module window.
Sub FilterDeleteVisible()
Dim range As range
Set range = Selection
range.AutoFilter Field:=2, Criteria1:="AC"
range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

Filter the Visible Rows and Delete with VBA in Excel

  • Next, close the Visual Basic window.
  • Subsequently, select the range of cells including the header.

Filter the Visible Rows and Delete with VBA in Excel

  • Now, select the Macros under the Developer tab.

  • As a result, a dialog box will pop out. Here, select FilterDeleteVisible in the Macro name.

  • Finally, press Run and you’ll only see the rows with product Cable.


1.2 Multiple Criteria

We can also apply the filter for multiple criteria with VBA. In the following dataset, our first criteria will be product AC. Subsequently, the second criteria will be the Net Sales amount Greater than $10,000. Hence, only the rows following both the conditions will be visible. Therefore, learn the steps below to perform the task and then delete the visible rows.

Filter the Visible Rows and Delete with VBA in Excel

STEPS:

  • In the beginning, click Visual Basic from the Developer tab.

Filter the Visible Rows and Delete with VBA in Excel

  • As a result, a new window will pop out. Here, select Module under the Insert tab.

Filter the Visible Rows and Delete with VBA in Excel

  • After that, the Module window will pop out, and there, copy and paste the following code into the module window.
Sub MultipleCriteria()
Dim rows As range
Dim range As range
Set range = Selection
range.AutoFilter Field:=2, Criteria1:="AC"
range.AutoFilter Field:=3, Criteria1:=">10000"
For Each rows In range.rows
Next
range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

Filter the Visible Rows and Delete with VBA in Excel

  • Then, close the Visual Basic window.
  • Now, select the range to work with.

Filter the Visible Rows and Delete with VBA in Excel

  • Afterward, select the Macros from the Developer tab.

  • Consequently, a dialog box will pop out and select MultipleCriteria in the Macro name.

  • At last, press Run and it’ll return the desired rows only.

Read More: How to Delete Filtered Rows in Excel (5 Methods)


Similar Readings:


2. Excel VBA to Delete Filtered Rows That are Hidden

So far, we’ve covered the visible rows. In our last method, we’ll show you the procedure to Filter and Delete the Hidden Rows. In the dataset given below, we’ll apply two criteria. The first one is the product AC and the second one is the Net Sales that are Greater than $10,000. Finally, we’ll delete the hidden rows which don’t follow both of the criteria.

Excel VBA to Delete Filtered Rows That are Hidden

STEPS:

  • First, select Visual Basic which you’ll find under the Developer tab.

Excel VBA to Delete Filtered Rows That are Hidden

  • Then, a new window will pop out, and here select Module from the Insert tab.

Excel VBA to Delete Filtered Rows That are Hidden

  • Consequently, the Module window will pop out. There, copy the following code and paste it into the window.
Sub FilterDeleteHidden()
Dim uni As range
Dim rows As range
Dim range As range
Set range = Selection
range.AutoFilter Field:=2, Criteria1:="AC"
range.AutoFilter Field:=3, Criteria1:=">10000"
For Each rows In range.rows
If rows.Hidden Then
If Not uni Is Nothing Then
Set uni = union(uni, rows)
Else
Set uni = rows
End If
End If
Next
uni.Delete
ActiveSheet.AutoFilterMode = False
End Sub

Excel VBA to Delete Filtered Rows That are Hidden

  • After that, close the Visual Basic window.
  • Now, select the range of cells.

Excel VBA to Delete Filtered Rows That are Hidden

  • Subsequently, select Macros from the Developer tab.

Excel VBA to Delete Filtered Rows That are Hidden

  • As a result, a dialog box will pop out. Here, select FilterDeleteHidden in the Macro name.

  • Next, press Run.
  • A dialog box will appear and there, press OK.

  • Eventually, it’ll delete the hidden rows and will return only the rows that were visible.

Read More: How to Delete Hidden Rows in Excel (3 Methods)


Conclusion

Henceforth, you will be able to Filter and Delete Rows with VBA in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

1 Comment
  1. Thanks. I have a workbook in which many thousands of rows needed to be deleted at a time. This was very slow, tens of seconds, using this method. I had an idea and found it to be very fast. Instead of deleting rows, I clear contents and then resort the data thus removing all the blank rows created. It is very fast and where appropriate, perhaps a good alternative to have in the toolbox

Leave a reply

ExcelDemy
Logo