VBA AutoFilter for Multiple Criteria

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
  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

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Which line of code gave the error?
 

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Which line of code gave the error?
The first line

Capture.JPG
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
You need to put that into your code, replacing this
VBA Code:
 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

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Capture.JPG


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
What is proposal?
 

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
That is the name of the sheet I am working on
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Is the the sheet name as seen on the tab, or is the sheet codename as seen in the vb editor project window?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,525
Messages
5,548,556
Members
410,849
Latest member
DannyNg
Top