[Solved] Advanced Filter Criteria Range Help

[bino1121]

This is the code for an advanced filter I am running I am trying to find a way to make the Range("A1:A97") to be xlDown Instead I as the range values for both ranges listed in the code need to be a variable size not a defined range as the lists could be bigger or smaller then the amount listed.


Sub FilterTest ()

Range("A3, Range("A3").End(xlDown)).AdvancedFilter Action:= xlFilterInPlace, _
CriteriaRange:=Sheets("Facilities").Range("A1:A97"), Unique:= False

End Sub

 

[Yousuf Shovon]

This is the code for an advanced filter I am running I am trying to find a way to make the Range("A1:A97") to be xlDown Instead I as the range values for both ranges listed in the code need to be a variable size not a defined range as the lists could be bigger or smaller then the amount listed.


Sub FilterTest ()

Range("A3, Range("A3").End(xlDown)).AdvancedFilter Action:= xlFilterInPlace, _
CriteriaRange:=Sheets("Facilities").Range("A1:A97"), Unique:= False

End Sub

Dear BINO,

Welcome to our ExcelDemy forum! Thank you for the thorough explanation. I understand you wish to set the Criteria range dynamically and as a variable size. Just like you have taken the Filter range. Fortunately, you can use the xlUp keyword to find the last non-empty cell in the column instead of using a static range size, to make the two ranges dynamic and variable size. The modified code is:

Sub FilterTest()

Dim lastRow As Long

    ' Find last row in column A
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    ' Define data range
    Range("A3", Range("A" & lastRow)).AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:=Sheets("Facilities").Range("A1", Sheets("Facilities").Range("A" & Rows.Count).End(xlUp)), Unique:=False

End Sub

Try this code and let me know if it works. Further, if you like to apply multiple criteria in your Advanced filter, go to https://www.exceldemy.com/excel-vba-advanced-filter-multiple-criteria-range/
I have attached the Excel file for a better understanding. Good luck!

Regards,
Yousuf Shovon

 

[bino1121]

Is it possible while using advanced filter in VBA code to add wildcards in the VBA code so that it only returns those exact values and not something that would contain the string of numbers and letters? while also still using the facilities list from above to be what the data set is filtered by? For example, if I run an advanced filter for the color red and in my sheet I have Red1, Red2, Red3,.... Etc then it is going to return all values that include just RED (which means those including a # after them Ex: Red1) instead of just the one color RED.

 

[Yousuf Shovon]

Is it possible while using advanced filter in VBA code to add wildcards in the VBA code so that it only returns those exact values and not something that would contain the string of numbers and letters? while also still using the facilities list from above to be what the data set is filtered by? For example, if I run an advanced filter for the color red and in my sheet I have Red1, Red2, Red3,.... Etc then it is going to return all values that include just RED (which means those including a # after them Ex: Red1) instead of just the one color RED.

Dear BINO,

Nice to hear from you again. I understand you wish to add wildcards in the VBA code that returns only the exact values. Fortunately, it is possible to use wildcards in the AdvancedFilter criteria in VBA code to filter data that match specific patterns. Here is the VBA code to do so.

Sub FilterTest()

Dim lastRow As Long

' Find last row in column A
lastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A3", Range("A" & lastRow)).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("Facilities").Range("A1", Sheets("Facilities").Range("A" & Rows.Count).End(xlUp)), Unique:=False

Range("A3", Range("A" & lastRow)).AutoFilter Field:=1, Criteria1:="Red*", Operator:=xlAnd

End Sub

I have attached the Excel file for a better understanding. Try it and let me know if it works.

Regards,
Yousuf Shovon

 

[bino1121]

Sub FilterTest() Dim lastRow As Long ' Find last row in column A lastRow = Range("A" & Rows.Count).End(xlUp).Row Range("A3", Range("A" & lastRow)).AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=Sheets("Facilities").Range("A1", Sheets("Facilities").Range("A" & Rows.Count).End(xlUp)), Unique:=False Range("A3", Range("A" & lastRow)).AutoFilter Field:=1, Criteria1:="Red*", Operator:=xlAnd End Sub

Right so for this code to run it would be my understanding that I would have to input each search item individually? "Red*" Where as the code I am running is advanced filtering Sheet1 for a list of values on Sheet2. How would I add a wildcard search for a list and not a specific term I guess is my question.

 

[Yousuf Shovon]

Right so for this code to run it would be my understanding that I would have to input each search item individually? "Red*" Where as the code I am running is advanced filtering Sheet1 for a list of values on Sheet2. How would I add a wildcard search for a list and not a specific term I guess is my question.

Hello Bino,

Answering your question to input each search item individually, I have taken an input box where you can search for a list and it doesn't have to be a specific term. You can search for any item on that list now. Here is the modified code:

Sub FilterTest()
   
    Dim lastRow As Long
    Dim temp As String
   
    temp = InputBox("Enter search term:")
     
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
     
    Range("A1", Range("A" & lastRow)).AdvancedFilter Action:=xlFilterInPlace, _
        CriteriaRange:=Sheets("Facilities").Range("A1", Sheets("Facilities").Range("A" & Rows.Count).End(xlUp)), Unique:=False
   
    Range("A1", Range("A" & lastRow)).AutoFilter Field:=1, Criteria1:="*" & temp & "*", Operator:=xlAnd

End Sub

Attached is an Excel file for a better understanding. Please, let me know if it works. Thank you.

Regards,
Yousuf Shovon