asked on

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.

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

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

aikimark

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.

Eoin OSullivan

ASKER

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?

Eoin OSullivan

ASKER

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