XLA routines: EE_FilterAndRemove
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
»
- Nick's blog
- Login or register to post comments
- 3122 reads
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago