VBA Multiple <> Filter

sachavez

Active Member
Joined
May 22, 2009
Messages
446
Hello!

I'm looking to delete all rows not containing the following in column E. I have the following code, but it's not working. Any ideas?

Code:
With ActiveSheet.UsedRange
       .AutoFilter field:=5, Criteria1:=Array("<>PN", "<>RN", "<>DR", "<>TA"), Operator:=xlFilterValues
       .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Thanks!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
You are limited to 2 values when using <> in an autofilter.
Have a look at advanced filters instead https://www.contextures.com/xladvfilter01.html
Alternatively if the values are in Col E are set filter on the values you want to delete.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,736
Office Version
365
Platform
Windows
You could also try AutoFilter with the criteria built up this way.

Code:
Sub AutoFltrMultiNotEqual()
  Dim sCrit As String
  Dim a As Variant, itm As Variant
  
  With ActiveSheet.UsedRange
    a = .Columns(5).Value
    For Each itm In a
      Select Case itm
        Case "PN", "RN", "DR", "TA"
        Case Else: sCrit = sCrit & "|" & itm
      End Select
    Next itm
    .AutoFilter Field:=5, Criteria1:=Split(Mid(sCrit, 2), "|"), Operator:=xlFilterValues
    .Offset(1).EntireRow.Delete
    .AutoFilter
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,090,493
Messages
5,414,881
Members
403,550
Latest member
Haima1

This Week's Hot Topics

Top