Selecting AutoFilter option based on ComboBox value

[clmarquez]

Looking for some help on this one:

I have a combobox on Sheet1 that has multiple names available.
Whatever name is selected is linked to cell A1. On Sheet2, I have th
same names listed in row A. What I want to do is when a name i
selected in the combobox, I want to go to Sheet2, find the cell in ro
A that has the matching name, and then pick the AutoFilter button tha
is in the same column but 4 rows down from the name, and filter o
(NonBlanks).

Any help is greatly appreciated...thanks

 

[Tom Ogilvy]

This requires that the autofilter dropdowns are already in place on sheet2
(which you imply is the case)

Sub SetAutofilter()
Dim rng as Range, res as Variant
With Worksheets("Sheet2")
Set rng = .Rows(1)
End With
res = Application.Match(Worksheets("Sheet1").Range("A1"), _
rng, 0)
If Worksheets("Sheet2").FilterMode Then
Worksheets("sheet2").ShowAllData
End If
If Not IsError(res) Then
Worksheets("Sheet2").AutoFilter.Range.AutoFilter _
Field:=res, Criteria1:="<>"
End If
End Sub

 

[clmarquez]

Worked like a charm...

I like how you added the lines to unfilter the data, just in case it
was already filtered in some way - it is definitely needed.

Thanks,
Kez