Filtering Data

AutoFilter

If you use the Selection.AutoFilter Filter:=2 then it will reset just column 2, if you use Selection.AutoFilter then it will reset on all columns and will remove the AutoFilter completely.

Worksheets("Data").Select 
Worksheets("Data").AutoFilterMode = True 
Range("A2").AutoFilter 
Range("A2").AutoFilter Field:=3, Criteria1:="filter" 

Show all Records

If ActiveSheet.AutoFilter.FilterMode Then 
   ActiveSheet.AutoFilter.ShowAllData 
End If 

Toggle on or off

If Not ActiveSheet.AutoFilterMode Then 
   ActiveSheet.Range("A1").AutoFilter 
End If 

Remove a filter if it exists

Worksheets("Data").AutoFilterMode = False 

ActiveWindow.AutoFilterDateGrouping = Not ActiveWindow.AutoFilterDateGrouping 

If ActiveSheet.AutoFilterMode Then 
   MsgBox "There is a Filter" 
Else 
   MsgBox "There is no Filter" 
   Selection.AutoFilter 
End If 

Range("B2").Select 

The following line adds the autofilter to the currentregion of the active cell.

ActiveCell.AutoFilter 

ActiveCell.AutoFilter Criteria="String" 
ActiveCell.AutoFilter Field:=1, Criteria:=">10/07/2004", Operator:=xlAnd, Criteria2:="<16/07/2002" 

Range("Database").Select 

Removing all existing filters

ActiveSheet.ShowAllData 

The above line causes run-time error if filtering is not applied - TEST THIS

On Error Resume Next 
ActiveSheet.ShowAllData 

Advanced Filter

Copy To Range

Dim rgeCriteria As Range 
Dim rgeExtract As Range 

Set rgeCriteria = Range("A2:A4") 
Set rgeExtract = Range("C2") 
Range("B2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ 
                                         CriteriaRange:=rgeCriteria, _ 
                                         CopyToRange:=rgeExtract 

Getting a List of Unique Items
Here are four examples of counting unique values in a list. Each of these examples creates an array of the unique items, so they can be modified to to those arrays for a purpose other than just counting the unique items.

Sub cMethodAdvFilter() 
    CountUniqueByAdvFilter Selection.Address 
End Sub 

Sub CountUniqueByAdvFilter(mRange As String) 
    Dim TheRange As String 
    Application.ScreenUpdating = False 
    
    TheRange = "'[" & ActiveWorkbook.Name & _  
    "]" & ActiveSheet.Name & "'!" & mRange  
    
    Workbooks.Add 
    
    Range(TheRange).AdvancedFilter Action:=xlFilterCopy, CopyToRange _ 
:=Range("A1"), Unique:=True 
    
    MsgBox Application.WorksheetFunction.CountA(Range("A:A")) 
    
    ActiveWorkbook.Close False 
    
    Application.ScreenUpdating = True 
End Sub