XLA routines: EE_FilterAndRemove

Nick's picture
EE_FilterAndRemove is a hugely useful VBA routine that uses sorting to remove large amounts of data from a table. Specify your table of data, the heading name, and the criteria. - designed to work well with vast amounts of data
Sub EE_FilterAndRemove(rngTable As range, strHeading As String, strCriteria As String)
    Dim rngTblData      As range
    Dim rngSortData     As range
    Dim intHeadCol      As Integer
 
'http://excelexperts.com/xla-routines-eeFilterAndRemove    for updates on this sub routine
    Set rngTblData = Intersect(rngTable, rngTable.Offset(1))
    intHeadCol = Application.WorksheetFunction.Match(strHeading, rngTable.Rows(1), 0)
 
    rngTable.AutoFilter Field:=intHeadCol, Criteria1:=strCriteria
    On Error Resume Next
        rngTblData.SpecialCells(xlCellTypeVisible).Clear
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
    'Clear Filter
    If rngTable.Parent.AutoFilterMode = True Then rngTable.Parent.AutoFilterMode = False
 
    'Sort
    rngTable.Parent.Sort.SortFields.Clear
    Set rngSortData = rngTable.Cells(2, intHeadCol).Resize(rngTable.Rows.Count - 1)
    rngTable.Parent.Sort.SortFields.Add Key:=rngSortData, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
    With rngTable.Parent.Sort
        .SetRange rngTable
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    Set rngTblData = Nothing
    Set rngSortData = Nothing
End Sub