Welcome to Excel Avon

## Advanced filter in Excel VBA

DOWNLOAD USED EXCEL FROM HERE>>

Today’s article is about *Advanced filter* in Excel VBA, today we are going to understand How to use *Advanced filter* in Excel VBA, when you are dealing with large amount of data where you want to apply multiple types of filters at the same time use *advanced filter*s. Advanced filter VBA allows us to filter data based on a *Criteria* range. This allows us to do more advanced filtering than the standard filtering on the worksheet.

You can also remove duplicates from the data by using advanced filters. You should be familiar with creating an advanced filter in Excel before attempting to create an advanced filter within VBA.

## Use Advanced Filter in Excel VBA

### Syntax

Expression.AdvancedFilter (Action, [CriteriaRange], [CopyToRange], [Unique])

**Expression** = Expression can be set as a Range, or the Range can be assigned to a variable and that variable can be used.

**Action** [Required] = xlFilterInPlace or xlFilterCopy.

*xlFilterInPlace* – Filter the original data.

*xlFilterCopy* – Copy the filter results to a new range.

**CriteriaRange** [Optional] = Range of the criteria used for filtering the data.

**CopyToRange [**Optional**] = **Destination range if Action is set to xlFilterCopy.

**Unique [**Optional**] = **True for unique records only.

We have some data in which we will apply advanced filter and find the result.

We have to go like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Once we insert the module, we will write a subroutine for advanced filter.

Sub UseAdvancedFilter() End Sub

Here we will define variables for input worksheet and output worksheet.

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet End Sub

Now after defining the variables of the worksheet, we will define the variables for long and range.

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range End Sub

Now we will set up the *Inputworksheet* for the input data. And with this we will set sheet2 for output data or result. Result, which is after running code, sheet 1 is output worksheet.

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 End Sub

In the worksheet we will set the Lr (last row) of data.

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row End Sub

Here we will set List Range

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row Set ListRng = iWS.Range("A1:C" & Lr) End Sub

Now we will set Criteria Range. and the Criteria Range will be in the output worksheet.

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row Set ListRng = iWS.Range("A1:C" & Lr) Set crRng = oWS.Range("A1:C2") End Sub

Now we will add copy range.

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count). End(xlUp). Row Set ListRng = iWS.Range("A1:C" & Lr) Set crRng = oWS.Range("A1:C2") Set CopyRng = oWS.Range("A4:C4") End Sub

Now we will add the advanced filter formula, we will Select for Action Argument xlFilterCopy. In unique argument we will select *false*.

Sub UseAdvancedFilter() Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row Set ListRng = iWS.Range("A1:C" & Lr) Set crRng = oWS.Range("A1:C2") Set CopyRng = oWS.Range("A4:C4") ListRng.AdvancedFilter xlFilterCopy, crRng, CopyRng, False End Sub

After this we will go to the worksheet and then going to the *Insert* button, we will assign a search button, Drag will select macro and rename button.

Now we will give a Criteria in the criteria range and then click on the search button.

We will search by giving multiple criteria in it. We have given 2 criteria, the first one is Florida State and the second one is people named K.

This time we will give 3 criteria and search, here we have given 3 criteria, first which is state NJ, second which name starts with C, and third date of birth. After click run button result is in front of you.

Come on, we have learned to filter with criteria, but now we have to assign reset button for it, that means we have to write code for reset button, but we will not write the code, copy and paste the same code which we made for filter.

We have to write subroutine for reset data and rest of the code we will use filter only.

Sub UseAdvancedFilter()Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row Set ListRng = iWS.Range("A1:C" & Lr) Set crRng = oWS.Range("A1:C2") Set CopyRng = oWS.Range("A4:C4") ListRng.AdvancedFilter xlFilterCopy, crRng, CopyRng, FalseEnd SubSub ResetData()Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row Set ListRng = iWS.Range("A1:C" & Lr) Set crRng = oWS.Range("A1:C2") Set CopyRng = oWS.Range("A4:C4") ListRng.AdvancedFilter xlFilterCopy, crRng, CopyRng, FalseEnd Sub

We have to assign a macro to clear it, so we will empty the Criteria Range (CrRange). as you can see below.

Sub UseAdvancedFilter()Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row Set ListRng = iWS.Range("A1:C" & Lr) Set crRng = oWS.Range("A1:C2") Set CopyRng = oWS.Range("A4:C4") ListRng.AdvancedFilter xlFilterCopy, crRng, CopyRng, FalseEnd SubSub ResetData()Dim iWS As Worksheet Dim oWS As Worksheet Dim Lr As Long Dim crRng As Range Dim CopyRng As Range Dim ListRng As Range Set iWS = Sheet2 Set oWS = Sheet1 Lr = iWS.Range("A" & Rows.Count).End(xlUp).Row Set ListRng = iWS.Range("A1:C" & Lr) Set crRng = oWS.Range("A1:C2") Set CopyRng = oWS.Range("A4:C4")crRng.Rows(2).Value = ""ListRng.AdvancedFilter xlFilterCopy, crRng, CopyRng, FalseEnd Sub

After writing the code, we will go to the worksheet, insert another button which will be for reset data, assign the macro, drag and select and then name the button as we have given reset button. As you can see, we have given two images below, the second image is after clicking the reset button. By clicking the reset data button, the given criteria will be removed, the criteria range will be blank, and the complete data will appear without any criteria.

The first image is Result after applying criteria.

The second image is after clicking the reset button given criteria will be removed.

So, I hope you have understood How to use Advanced filter in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FROM HERE>>

**You can also see well-explained video here about use Advanced filter in Excel VBA**