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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
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
44,424
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,095,726
Messages
5,446,151
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top