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

 

[Fluff]

How about

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

 

[natheplas]

Hi Fluff,

Thanks for coming back to me so quick.

I copied and pasted your code and I get runtime error 1004 - Autofilter method of range class failed.

What have I done wrong?

 

[De Bruce]

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

You can try the code below incase you're still getting an error.
Code:

Sub FilterRows()
Dim x As String
x = Worksheets("Report").Range("B1")

With Worksheets("Report")
.Range("A5:S5").AutoFilter Field:=1, Criteria1:=x, Operator:xlAnd
.Range("A5:S5").AutoFilter Field:=7, Criteria1:=>0, Operator:xlAnd
End With
End Sub

Using a variable helps. I hope this works.

 

[Fluff]

Is the data in a formal table?

 

[natheplas]

Hi De Bruce,

Thank you so much for trying another solution for me.

However,

.Range("A5:S5").AutoFilter Field:=1, Criteria1:=x, Operator:xlAnd
.Range("A5:S5").AutoFilter Field:=7, Criteria1:=>0, Operator:xlAnd

appear in red and I get a 'compile error - syntax error' when I try to run it.

Any ideas?

 

[natheplas]

Sorry forgive my ignorance! What's a formal table?

 

[Fluff]

If you click in the data & then look just above the ribbon is there something that says TABLE TOOLS & below that DESIGN

 

[natheplas]

Yeah I believe it is. I've posted this image https://imgur.com/a/ndVbZqx if it helps? Thanks for this!

 

[Fluff]

In that case try

Sub FilterRows()
With Worksheets("Report").ListObjects("vw_StockBasic")
   .Range.AutoFilter Field:=1, Criteria1:=.Parent.Range("B1").Value
   .Range.AutoFilter Field:=7, Criteria1:=">0"
End With
End Sub