Filtering Out Desired Criteria and Deleting All Others

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
126
Hello,

I'm in the middle of coding a macro that will filter for various criteria in order to delete them from a report that has a lot of extraneous data. For example, take the code below:

VBA Code:
ActiveSheet.Range("A4:O50000").AutoFilter Field:=3, Criteria1:="PK BAG"
ActiveSheet.Range("A4:O50000").AutoFilter Field:=4, Criteria1:="SKIP"
On Error Resume Next
Range("A5:A50000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.ShowAllData
    
ActiveSheet.Range("A4:O50000").AutoFilter Field:=3, Criteria1:="PK BAG"
ActiveSheet.Range("A4:O50000").AutoFilter Field:=4, Criteria1:="LQTY"
On Error Resume Next
Range("A5:A50000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.ShowAllData

The only criteria I want to keep in Field 4 for category PK BAG is called GRABS, so I coded to have SKIP and LQTY deleted. What I would like to do for the sake of shortening the time to code this for a few dozen categories is to declare the criterion per category I want to keep and delete all others instead of listing one at a time all the criteria that should be deleted (e.g. if it's not GRABS then delete).

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub FrenchCelt()
With ActiveSheet
   .Range("A4:O50000").AutoFilter Field:=3, Criteria1:="PK BAG"
   .Range("A4:O50000").AutoFilter Field:=4, Criteria1:="<>GRABS"
   .AutoFilter.Range.Offset(1).EntireRow.Delete
   .ShowAllData
End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,223
Messages
5,576,827
Members
412,748
Latest member
MikeyP14
Top