Autifilter only working for 2 criteria, delete visible rows not header

[Rowland Hamilton]

Folks:

PART I: How come I can't get my autofilter code to work for more than 2 criteria? I keep getting "Criteria doesn't exist" but why should that matter if I'm using "Or"?

If you have solution to only PART I or PART II, please respond.

PART II : Do you have a better idea for deleting the visible rows of filtered data below header row 3? How else can I select that range (something to do with choosing fields first through last?)?

Sub Remove_Totals_Blanks_Duplicate_Headers()
      'Remove Totals, Blanks and Duplicate Headers
 
'PART I    
    Sheets("Incoming").Select
    ActiveSheet.AutoFilterMode = False
 
    Rows("3:3").Select
    Selection.AutoFilter
 
 'Why doesn't filter work past the first 2 criteria?
    Range("A3:I3").AutoFilter Field:=3, _
                        Criteria1:="*Cost*", _
        Operator:=xlOr, Criteria2:="*cost*", _
        Operator:=xlOr, Criteria3:="*Total*", _
        Operator:=xlOr, Criteria4:="*total*", Operator:=xlOr, Criteria5:="="
 
'PART II  
'Now how do I delete the filtered, visible, contiguous Field 3 rows below my header row 3?
 
    'I think this would do it (worked in my recorderd version)
         'Range("C3").Select
         'Selection.End(xlDown).Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.EntireRow.Delete
 
  End Sub

Thank you, Rowland

 

[andiemac]

What version of Excel are you using? It's possible that 2003 only allows 2 criteria???

Once you have your data filtered to your liking:

Range("a3", Selection.SpecialCells(xlCellTypeLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete

 

[Rowland Hamilton]

Andiemac: Thanks, actually using Excel 2010 program and file.

For deleting visible rows, will your code keep my header row #3?

-Rowland

 

[andiemac]

Sorry, no. I thought you wanted it contiguous from row 3. Alter the Range("a3":... line to A4. The next step, xlCellTypeVisible, only chooses visible from the selected range.

 

[Marcelo Branco]

Maybe this can help
http://www.rondebruin.nl/delete.htm

Look for
More than 2 criteria

M.

 

[Rowland Hamilton]

Marcelo: Thank you. Says you need to use an array if you have more than 2 criteria. Got it. Need to try it now.

 

[Rowland Hamilton]

You're right, I was able to change it in the test file for starting row and column. - Need apply to my file and data now - Row

Option Explicit
'For more then two Criteria you can loop through the words in the array.
'Note: This is also working if you use one word in the array.
Sub Delete_with_Autofilter_Array()
    Dim rng As Range
    Dim calcmode As Long
    Dim myArr As Variant
    Dim I As Long
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Fill in the values that you want to delete
    myArr = Array("ron", "Dave", "Jelle")
    For I = LBound(myArr) To UBound(myArr)
        'Sheet with the data, you can also use Sheets("MySheet")
        With ActiveSheet
            'Firstly, remove the AutoFilter
            .AutoFilterMode = False
            'Apply the filter
            .Range("B5:B" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
            Set rng = Nothing
            With .AutoFilter.Range
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then rng.EntireRow.Delete
            End With
            'Remove the AutoFilter
            .AutoFilterMode = False
        End With
    Next I
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
End Sub

 

[Rowland Hamilton]

Folks:

Can you help? My filter works but no data gets deleted:

Won't work in my file but works in test file from link. When I modified the test file, adding spaces, replacing the terms with my terms in the Sheet and then modifying the code:

myArr = Array("*Cost*", "*cost*", "*Total*", "*total*", "")

and

.Range("C3:C" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)

In the test file no problem, but my file, I step through it and watch the filters working but no rows are deleted. I saved teh test file as an Excel 2010 xlsm file same as mine and it still worked.

Thanks, Rowland

 

[Marcelo Branco]

Hi Rowland,

Seeing your array i think that in fact you have only 2 criteria: Total and Cost - you dont neeed Total, total, Cost, cost.

The filter is not case sensitive.

Could you post a small sample of your data. To do this: select a relevant part of your data, say 10 rows including the header(s); put borders; copy (Ctrl+C); and paste (Ctrl+V) in the forum reply-page.

M.

 

[Rowland Hamilton]

Marcelo and Andiemac:

Thank, Marcelo,

I'll try see what I can get you and post my current solution later. Meanwhile I got it to work by using andiemac's code for deleting visible rows, the 2 criteria filter at a time method, and my method for creating a contiguous data column so the filter array knows the bottom of the data.

Thanks again, Andiemac.

Thanks all,
Rowland