VBA AutoFilter for Multiple Criteria

[chethead]

Hello,

I don't really understand VBA but try to cobble things together from what I find online. However, I cannot figure this out. I've got 3 items I am looking to filter based on dropdown lists. The headers are Status, Estimator and Manager and the cells that they represent where the dropdown lists exist are F2, G2 and H2. The first VBA I tried works, but only if I have all thee lists populated:

Sheets("Proposals").Range("A4").AutoFilter Field:=6, Criteria1:=Cells(2, 6).Value
Sheets("Proposals").Range("A4").AutoFilter Field:=7, Criteria1:=Cells(2, 7).Value
Sheets("Proposals").Range("A4").AutoFilter Field:=8, Criteria1:=Cells(2, 8).Value

Very rarely would we have all 3 items selected so I tried something that appears to have wildcards where I don't have to have items selected in all three dropdowns:

Sub Filter()

    Dim Status As String
    Dim Estimator As String
    Dim Manager As String
    Dim rng As Range
   
Status = Proposals.Range("F2").Value
Estimator = Proposals.Range("G2").Value
Manager = Proposals.Range("H2").Value

  Set rng = Proposals.Range("A4")

  If Status = "" Then Status = "*"
  If Estimator = "" Then Estimator = "*"
  If Manager = "" Then Manager = "*"

        rng.AutoFilter Field:=6, Criteria1:=Status & "*"
        rng.AutoFilter Field:=7, Criteria1:=Estimator & "*"
        rng.AutoFilter Field:=8, Criteria1:=Manager & "*"
   
End Sub

This doesn't seem to work at all. I get a Run-time error '424': Object required.

Any help would be appreciated!

Thank you,

Rob

 

[Fluff]

How about

  If Status = "" Then rng.AutoFilter Field:=6 Else rng.AutoFilter Field:=6, Criteria1:=Status
  If Estimator = "" Then rng.AutoFilter Field:=7 Else rng.AutoFilter Field:=7, Criteria1:=Estimator
  If Manager = "" Then rng.AutoFilter Field:=8 Else rng.AutoFilter Field:=8, Criteria1:=Manager

 

[chethead]

Thank you Fluff. I copied and pasted your idea into the first VBA code I have listed above but am now receiving the Run-time error '424': Object required on that spreadsheet too

 

[Fluff]

Which line of code gave the error?

 

[chethead]

Which line of code gave the error?

The first line

 

[Fluff]

You need to put that into your code, replacing this

 If Status = "" Then Status = "*"
  If Estimator = "" Then Estimator = "*"
  If Manager = "" Then Manager = "*"

        rng.AutoFilter Field:=6, Criteria1:=Status & "*"
        rng.AutoFilter Field:=7, Criteria1:=Estimator & "*"
        rng.AutoFilter Field:=8, Criteria1:=Manager & "*"

 

[chethead]

Oh - I did not understand initially. Still same error though

Cell F2 is where I choose between 3 status types, it is a drop down. G2 and H2 are similar, choosing estimator and manager respectively.

Any other thoughts?

 

[Fluff]

What is proposal?

 

[chethead]

That is the name of the sheet I am working on

 

[Fluff]

Is the the sheet name as seen on the tab, or is the sheet codename as seen in the vb editor project window?