Whenever Excel creates a filtered list on a worksheet, it creates a hidden named range in the background in the Name Manager. This range is not normally visible if you call up the name manager. Use the below code to make your hidden named ranges visible in the name manager (prior to using it, set … Read more
To filter on multiple wildcards, create a variant array of wildcard matches and then use the array of full values with the standard AutoFilter method. You can minimize the array by putting a dictionary object to use with its unique index property. Consider the following sample data. Run this code. Sub multiWildcards() Dim v … Read more
AutoFilterMode will be True if engaged, regardless of whether there is actually a filter applied to a specific column or not. When this happens, ActiveSheet.ShowAllData will still run, throwing an error (because there is no actual filtering). I had the same issue and got it working with If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then ActiveSheet.ShowAllData … Read more
Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered. Sometimes I have found that one can use the numeric value of the date, especially when dealing with “dates between” Criteria1:= “>” & CDbl([datecell]) Criteria2:= “<=” & CDbl(WorksheetFunction.EoMonth([datecell], 3)) Note that the above need … Read more
While there is a maximum of two direct wildcards per field in the AutoFilter method, pattern matching can be used to create an array that replaces the wildcards with the Operator:=xlFilterValues option. A Select Case statement helps the wildcard matching. The second field is a simple Criteria1 and Criteria2 direct match with a Operator:=xlOr joining … Read more
Remember the goal is to delete the non-matching rows; AutoFilter is only one tool to help achieve the goal. If AutoFilter does not meet your needs, pick another method. Consider: Sub AllBut() Dim rTable As Range, r As Range Dim rDelete As Range Set rTable = Selection Set rDelete = Nothing For Each r In … Read more