Wrapping a Class Around the AdvancedFilter Method

Classes are a tool in object-oriented programming that make programming much simpler. One class that I have developed in VBA is a class that makes using the AdvancedFilter method extremely easy and useful. In fact, I like it so much that I stopped using the regular find method. In the coming weeks I will demonstrate how this was done.

 This is how the AdvancedFilter method works:

  1.   Use a predefined range to filter.
  2. Create a range that has the filter criteria.
  3. Have a range for copying the results (or filter in place).
  4. Filter for unique values only.
Drawbacks of the AdvancedFilter method:
  1. Mixed data in predefined range can give odd/incorrect results.
  2. Is slow compared to a database search or even a manual looped search.
  3. Need a special range to perform search.
Advantages of the AdvancedFilter method:
  1.  Can use familiar Excel formulas in search terms.
  2. Great for searching database structured data in Excel.
  3. The copy method is pretty fast if you are planning on copying the data.
This is how the class will work (Of course, you could use a function to wrap this code to make it even simpler for simple searches - which I have done):

Sub AdvancedFilterClassExample()
    Dim iIndex As Integer
    Dim rResult As Range
    Dim clsSearch As Search

    'clsSearch.ColumnUnique = 1
    'clsSearch.CopyUniqueTo = Range("A101")

    clsSearch.IncludeHeaderInResults = True
    clsSearch.RangeToFilter = Range("A1:Y100")
    clsSearch.FilterLocation = Range("Z1")
    iIndex = clsSearch.Add("George")
    clsSearch(iIndex).Header = "First Name"
    clsSearch(iIndex).match_type = BasicSearch
    clsSearch(iIndex).Header_Operator = AndOperator

    iIndex = clsSearch.Add("*")
    clsSearch(iIndex).Header = "Last Name"
    clsSearch(iIndex).match_type = WildCardOnly
    clsSearch(iIndex).Header_Operator = OrOperator

    Debug.Print clsSearch.Count

    Set rResult = clsSearch.Filter

End Sub

Match Types:


Header Types: