Excel VBA autofilter all but three

by

Remember the goal is to delete the non-matching rows; AutoFilter is only one tool to help achieve the goal. If AutoFilter does not meet your needs, pick another method. Consider:

Sub AllBut()
    Dim rTable As Range, r As Range
    Dim rDelete As Range
    Set rTable = Selection
    Set rDelete = Nothing
    For Each r In rTable.Columns(7).Cells
        v = r.Value
        If v <> "101" And v <> "102" And v <> "103" Then
            If rDelete Is Nothing Then
                Set rDelete = r
            Else
                Set rDelete = Union(r, rDelete)
            End If
        End If
    Next

    If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

Here we select the block of data to be processed (not including the header row). The macro sweeps down column #7 of that block and deletes any row that does not match the criteria.

All that will remain are the 101’s, the 102’s, and the 103’s.

More Related Contents:

  1. Autofilter with column formatted as date
  2. ShowAllData method of Worksheet class failed
  3. AutoFilter – Use of SpecialCells
  4. Save excel file multiple times with file names in another workbook.
  5. Function to convert column number to letter?
  6. Read/Parse text file line by line in VBA
  7. Better way to find last used row
  8. Excel VBA: Answer gets “stuck”
  9. 2 Dimensional array from range [closed]
  10. filter out multiple criteria using excel vba
  11. What is the difference between dim and set in vba
  12. Change date format using substitute or replace
  13. Getting the actual usedrange
  14. Delete a row in Excel VBA
  15. VBA: Convert Text to Number
  16. How can I run VBA code each time a cell gets its value changed by a formula?
  17. Referencing Excel Userform Control Name Value From String (VBA)
  18. Refreshing all the pivot tables in my excel workbook with a macro
  19. Countif With Multiple OR Criteria
  20. Vlookup to copy color of a cell
  21. How to delete rows in Excel based on criteria using VBA?
  22. Macro to export MS Word tables to Excel sheets
  23. VBA WinHTTP to download file from password proteced https website
  24. Excel VBA – select multiple columns not in sequential order
  25. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  26. Application.WorksheetFunction.Match method
  27. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  28. Excel Hide/Show all tabs on Ribbon except custom tab
  29. Macros Not Showing Up in Macro Table
  30. Excel telling me my blank cells aren’t blank

Leave a Comment