AutoFilter To delte rows with multiple criteria in different columns

JuanExcel

New Member
Joined
Apr 9, 2018
Messages
1
Hi everyone,

I am new here and new to VBA as well. I want to create an auto filter code that will delete rows based on criteria. I need to delete rows of one criteria in one column and then another criteria in a different column. I have the following code that will cover one criteria but I want to add the other criteria. I am trying to delete "DO NOT USE" from one and then "WA-KING" from another column.

Note: I had found this code on another forum but was trying to manipulate to work for me but it has not worked for me. This code may be atrocious

Code:
' This will be to filter for the 2 Criteria that needs to deleted
   Dim ContractNum As String
   Dim InvNum As String
   Dim FileRng As Range
   Dim FileLastRow As Long
   Dim File As Range
   Dim t As Single
   t = Timer
   
   Sheets("GL Sales Tax Amended FY18 Month").Select 'Activate the correct sheet
   
   FileLastRow = ActiveSheet.Range("A2").End(xlUp).Row
   
   'Identify the total range of lined, including the header.
   Set FileRng = Sheet1.Range("N2").End(xlUp)
   
   'Using the Autofilter method to elimate "Do not Use" rows
   Application.DisplayAlerts = False
   With FileRng
        .AutoFilter Field:=1, Criteria1:="DO NOT USE"
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
    End With
    Application.DisplayAlerts = True
    
    'Turn off the autofilter safely
    With Sheet1
        .AutoFilterMode = False
        If .FilterMode = True Then
            .ShowAllData
        End If
    End With
    
    MsgBox "Filtered the data 'AutoFilter' strategy completed in " & Timer - t & " seconds."
 
Last edited by a moderator:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi & welcome to MrExcel
This is based on filtering cols N & P change the numbers in red to suit
Code:
Sub chk()
' This will be to filter for the 2 Criteria that needs to deleted
   Dim t As Single
   t = Timer
   
   With Sheets("GL Sales Tax Amended FY18 Month") 'Activate the correct sheet
      If .AutoFilterMode Then .AutoFilterMode = False
      
      'Using the Autofilter method to elimate "Do not Use" rows
      With .UsedRange
         .AutoFilter [COLOR=#ff0000]14[/COLOR], "DO NOT USE"
         On Error Resume Next
         .Offset(1, 0).SpecialCells(xlVisible).EntireRow.Delete
         On Error GoTo 0
         .AutoFilter [COLOR=#ff0000]14[/COLOR]
         .AutoFilter [COLOR=#ff0000]16[/COLOR], "WA-KING"
         On Error Resume Next
         .Offset(1, 0).SpecialCells(xlVisible).EntireRow.Delete
         On Error GoTo 0
      End With
      
      'Turn off the autofilter safely
      .AutoFilterMode = False
   End With
   
   MsgBox "Filtered the data 'AutoFilter' strategy completed in " & Timer - t & " seconds."
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top