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
 

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
On the tab. The VB editor shows Sheet1 (Proposals).

But this is listed under Modules>Module 1

I really wish I understood what I was doing more. I would also take any recommendations on how to learn VBA.

Capture 1.JPG
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
Ok, change Proposals everywhere it occurs like
VBA Code:
Status = Sheets("Proposals").Range("F2").Value
 

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
It is the sheet name. In the editor, it says Sheet1 (Proposals)

This piece of code is listed under Module>Module 1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
Did you see post#12?
 

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Awesome! That seemed to do the trick! Thank you for your time and patience.

I would definitely still take any recommendation on how to learn VBA
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.

I cannot really recommend anything, as I've never read any books, or been on any courses. I have learnt most of what I know from helping people here.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,573
Messages
5,548,853
Members
410,880
Latest member
LBrand
Top