Drop Down List AutoFilter

[ozstrik3r69]

Dears good afternoon

I am wondering if someone can help me to find a solution for an autofilter.

I have actually 2 different sheets, one call "Cover" and another call "Import".
Sheet "Cover" contain a dropdown list on cell A2 with a list of countries and on sheet "Import", starting on Cell A4 finishing on cell AI2000 I have my datas.
On this same sheet "Import", on A4 I have all my countries (A4 is the header and list of countries start on A5)

What I am looking for is, to be able to autofilter list on "Import" sheet based on the value on sheet "Cover" in the drop down list.

I tried already several code but nothing worked correctly untill now.

May I ask if someone can help me ?

Waiting for your feedback
Regards

[ag273n]

it would be nice if you have the workbook attached...

[ozstrik3r69]

it would be nice if you have the workbook attached...

Thank you for your message
Attached in my first post

Regards

[ag273n]

try this. i added somethings on your file on the "Cover" worksheet.
and some code below:

Please Login or Register  to view this content.

take note, the reference on the code is on Range A2 of the cover worksheet. you cant insert columns before it because its hardcoded, unless you edit the code

[dflak]

First we have to set up a data validation in Cell E3 on the Cover Sheet.

Here is information on data validation: http://www.utteraccess.com/wiki/Data_Validation you want a list type validation and when it comes to the source, highlight the range W2:W18. Notice that I added an asterisk (*) in cell W18. Selection of this item selects all countries, effectively clearing the filter.

I gave Cell E3 a name. The easiest way to give a name to a fixed range of cells is to select the range - in this case, Cell E3 - and go to the name box (upper left on spreadsheet - it normally shows the top left cell of the range). Type in the name Sel_Country - I already did this. Excel now knows that Cell E3 on the Cover page is called Sel_Country no matter where you are in the workbook, or even in VB code.

I recorded a macro and modified it a bit.

Please Login or Register  to view this content.

It is always a good idea to tell Excel what sheet you want it to work on. One way of doing this is to set a "pointer" to the sheet. So the variable, sh, means Sheet Import-Export.

LRow is the last row with data in column A on this sheet. The formula shown is "standard" code for getting the last row in the column.

When I recorded the macro, it came up with a fixed number of rows. You may want to add or delete data. If the number of rows changes, then the code might fail so I changed the range to look at from $A$4:$U$500" to $A$4:$U$" & LRow. So it always reads to the last row.

Also when I recorded the macro, I used a fixed value. I replaced this with Range("Sel_Country") the country picked on Sheet Cover.

That's about all there is to say about the macro to filter the worksheet. All this code is in the ModFilterList module.

On the Cover Sheet Module there is a Change Event

Please Login or Register  to view this content.

The change event activates whenever the value of any cell on the sheet changes so, we put limits on when we want it to run the program. In this case if the cell being changed (the target) is cell $E$3, we want the program to run. Otherwise nothing happens.