VBA Macro - Auto Filter Multiple Criteria

[natheplas]

Hi,

I'm struggling with the below VBA. I have data In cells A6:S1176 and headings in cells A5:S5.
I want to filter column A based on the value in B1.
I then at the same time want to always filter out anything minus or below 0 in column G.
When I run this macro though I get this error message. 'Run-time error '1004, AutoFilter method of Range class failed'.
Please can someone assist? Not sure what Im doing wrong. If anything Ive asked doesn't make sense, please let me know.

Appreciate anyones help, thank you.

Sub FilterRows()
With Worksheets("Report").Range("A5:S5")
.AutoFilter field:=1, Criteria1:="B1"
.AutoFilter field:=7, Criteria1:=">0"
End With
End Sub

 

[natheplas]

That's almost perfect.

However, this line 'Range.AutoFilter Field:=1 It is filtering on column B now and not column A where I need it to and I can't put 0 there. So close.....

How can I change it so that it filters on column A and not B?

 

[natheplas]

I was able to amend worksheet to suit the code you wrote. Thank you Fluff! :D Legend.

 

[De Bruce]

You're welcome Natheplas,

To have a lasting solution, try recording a macro via
Developer tab. But first, your headings must have a filter arrow.
To achieve this, highlight your entire column headers (A5:S5).
On your excel ribbon click Home tab, under Editing group, click
Sort & Filter and then select Filter. A filter arrow automatically
appears on the left side of each column header (A5:S5).
Now you can record a macro while manually filtering using
Custom Filter in the options available . Check for the output
codes in modules, by default it is named as module 1.
I believe this should work.

 

[Fluff]

I was able to amend worksheet to suit the code you wrote. Thank you Fluff! :D Legend.

You're welcome & thanks for the feedback