Macro to autofilter Columns

[Howardc1001]

I would like a macro to autofilter all the columns and filter the values in the columns with header "bank charges" (will not always be in Col g as new columns are added from time to time)


I have tried to write code to do this, but it does not filter from row 5 and also get a run time error


"autofilter method of range class failed"

.Range("$A$5:$DZ" & LR).Autofilter Field:="Bank Charges", Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>" _

 Sub Autofilter()

Dim LR As Long

Sheets("Data").Select
LR = Range("A" & Rows.Count).End(xlUp).Row
    With Range("A5")
    .Range(Selection, Selection.End(xlToRight)).Autofilter
    .Range("$A$5:$DZ" & LR).Autofilter Field:="Bank Charges", Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>" _

     End With
      
      
      End Sub

I have also posted on https://www.mrexcel.com/forum/excel-...eo-blanks.html

[jeffreybrown]

Hi Howard,

How about...

Sub Autofilter()

    Dim x As Long
    Dim f As Range
    Set f = Rows("5:5").Find(what:="Bank Charges", LookIn:=xlValues)
    
    x = f.Column
    
    Sheets("Data").Select
    With Range("A5").CurrentRegion
        .Autofilter Field:=x, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"
    End With

End Sub

[Howardc1001]

Thanks Jeff. The code filter the data 100%

[TMS]

Try:

With Range("A5")
    .Autofilter
    .Resize(, 8).Autofilter _
        Field:=7, _
        Criteria1:="<>0", Operator:=xlAnd, _
        Criteria2:="<>"
End With

[Howardc1001]

Thanks for the help Trevor.

[jeffreybrown]

Good to hear Howard and thanks for the feedback. We are happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Howardc1001]

Hi Jeffrey


I have rechecked my data after running the macro and when I click on the filter all the values are unchecked. I want all the values checked, except zero (0.00) and blank items


I have attached my sample data


Kindly check & amend

[jeffreybrown]

Using the code below and your data, after the filter is applied only three rows are visible.

Sub Autofilter()
    Dim x As Long
    Set f = Rows("5:5").Find(what:="Bank Charges", LookIn:=xlValues)
    Sheets("Data").Select
    With Range("A5").CurrentRegion
        .Autofilter Field:=f.Column, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"
    End With
End Sub

[Howardc1001]

Thanks Jeffrey. Its working 100%