# 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.
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.RangeToFilter = Range("A1:Y100")
clsSearch.FilterLocation = Range("Z1")
clsSearch(iIndex).Header = "First Name"
clsSearch(iIndex).match_type = BasicSearch

clsSearch(iIndex).Header = "Last Name"
clsSearch(iIndex).match_type = WildCardOnly

Debug.Print clsSearch.Count

Set rResult = clsSearch.Filter

End Sub


Match Types:

    MatchType.BasicSearch
MatchType.MatchCase
MatchType.MatchCase_MatchEntireCellContents
MatchType.MatchEntireCellContents
MatchType.WildCardOnly


    HeaderOperator.AndOperator