Excel VBA required to filter rows on table

I'm not a VBA expert and need help with a VBA script as I believe the normal AutoFilter cannot achieve what I want.

I have an Excel spreadsheet  with a dropdown list of names of staff in cell A1.
You pick a staff name from the dropdown list and it is set in A1.

Below is a number of rows : A2 to A100
On each row column A has the same dropdown with one of the staff names pre-selected or left blank
On each row column B has the same dropdown with one of the staff names pre-selected or left blank
On each row in column C is a string of text

What I want to achieve is that you can choose a staff name on A1 .. and it will then filter rows 2 to 100 and look in the values in A & B that contain that staff name to only show those matching A or B rows which contain the staff name in A1.
Before.jpg
After.jpg
LVL 42
Eoin OSullivanConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Sub myFilter()

For Each c In Range(Range("A2"), Range("A" & Cells.Rows.Count).End(xlUp))
    If c = Range("A1") Or c.Offset(0, 1) = Range("A1") Then
        c.EntireRow.Hidden = False
    Else
        c.EntireRow.Hidden = True
    End If
Next
End Sub

Sub myUnfilter()
        Range("A:A").EntireRow.Hidden = False        
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Alternative:

In column D place formula:

=IF(OR(A2=$A$1,B2=$A$1),"Y",N")

Copy down as far as required and then filter on that column for Y.

Thanks
Rob H
0
aikimarkCommented:
You can use an AdvancedFilter to do this

Given the following data in my worksheet:
Name      	Helper     	Desc
John Brown	An Other	Additional text
An Other	An Other	Additional text
An Other	An Other	Additional text
John Brown	An Other	Additional text
John Brown	An Other	Additional text
An Other	John Brown	Additional text
An Other	An Other	Additional text
An Other	An Other	Additional text
John Brown	An Other	Additional text
An Other	An Other	Additional text
An Other	An Other	Additional text

Open in new window

I created a critera range that looks like this:
Name      	Helper
John Brown	
        	John Brown

Open in new window

Note: the two "John Brown" cells are on two different rows

I then used the AdvancedFilter, pointing to the criteria range and got the following filtered result:
Name      	Helper     	Desc
John Brown	An Other	Additional text
John Brown	An Other	Additional text
John Brown	An Other	Additional text
An Other	John Brown	Additional text
John Brown	An Other	Additional text

Open in new window

which seems to be what you wanted.

* You could use your dropdown selection to populate the criteria range and then apply the advancedfilter method
* The columns must have headers.  AdvancedFilters require it.
0
Eoin OSullivanConsultantAuthor Commented:
Thanks Rgonzo1971
That seems to work .. is there a way to apply that filter or makeit re-run every time the value in A1 changes when the dropdown is altered?
0
Eoin OSullivanConsultantAuthor Commented:
The script provided worked to generate the filter but it would have been nice to get the extra bit of code to re-run every time the field changed as requested.

I found the additional bit of code myself.

Awarding a B for a good but incomplete answer.  

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.