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