VBA AutoFilter for Multiple Criteria

chethead

New Member
Joined
Jul 23, 2015
Messages
45
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
 
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
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Ok, change Proposals everywhere it occurs like
VBA Code:
Status = Sheets("Proposals").Range("F2").Value
 
Upvote 0
It is the sheet name. In the editor, it says Sheet1 (Proposals)

This piece of code is listed under Module>Module 1
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top