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 filters. 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, False
End Sub
Sub 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, False
End 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, False
End Sub
Sub 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, False
End 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
