+ Reply to Thread
Results 1 to 5 of 5

Drop Down List AutoFilter

  1. #1
    Forum Contributor
    Join Date
    06-01-2017
    Location
    Santos
    MS-Off Ver
    Office 2016 - Windows 7
    Posts
    105

    Drop Down List AutoFilter

    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
    Attached Files Attached Files
    Last edited by ozstrik3r69; 05-25-2018 at 03:11 PM.

  2. #2
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: Drop Down List AutoFilter

    it would be nice if you have the workbook attached...
    don't worry, there's a lot of people that are far more confused than you
    but if you liked what i suggested. Click for me the "Add Reputation" - that way, we'd be both happy.

  3. #3
    Forum Contributor
    Join Date
    06-01-2017
    Location
    Santos
    MS-Off Ver
    Office 2016 - Windows 7
    Posts
    105

    Re: Drop Down List AutoFilter

    Quote Originally Posted by ag273n View Post
    it would be nice if you have the workbook attached...
    Thank you for your message
    Attached in my first post

    Regards

  4. #4
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: Drop Down List AutoFilter

    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
    Attached Files Attached Files

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,407

    Re: Drop Down List AutoFilter

    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.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Autofilter Macro to filter from a Drop Down List
    By Fibob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2015, 06:22 AM
  2. [SOLVED] autofilter-new sheet macro with drop down list
    By apatel615 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2013, 12:50 PM
  3. [SOLVED] Autofilter in VBA using a drop down list for criteria
    By MOONDRIFT47 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2012, 02:00 PM
  4. Replies: 5
    Last Post: 03-01-2012, 01:56 PM
  5. List AutoFilter drop-downs question/Excel 2003
    By Tavi in forum Excel General
    Replies: 2
    Last Post: 01-17-2012, 01:29 PM
  6. Autofilter's drop down list goes missing
    By Paul in forum Excel General
    Replies: 4
    Last Post: 06-08-2005, 11:05 AM
  7. [SOLVED] changing the font size on the drop down of a list (autofilter).
    By Culpeper Wood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2005, 09:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1