+ Reply to Thread
Results 1 to 9 of 9

Macro Autofilter same criteria across multiple columns

  1. #1
    Registered User
    Join Date
    11-05-2014
    Location
    Liverpool, England
    MS-Off Ver
    2010
    Posts
    5

    Macro Autofilter same criteria across multiple columns

    Hi,

    I'm wanting to insert a button(Macro) in excel 2010 which when pressed will autofilter a spreadsheet if a word (say London) appears in either column E, column F or G. Any help would be much appreciated

    Sheets("City").Select
    ActiveSheet.Range("$A$1:$G$547").AutoFilter Field:=5, Criteria1:="London"

    How can I get it to autofilter field 5, 6 or 7 for the same criteria? at the same time?

    Thank you.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Macro Autofilter same criteria across multiple columns

    Insert a helper column, which checks if your criteria value exists in any of the three target columns, or not. Filter on the helper column.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    11-05-2014
    Location
    Liverpool, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Macro Autofilter same criteria across multiple columns

    Thanks Olly;

    I can't quite figure out how to do a helper column... I basically have a spreadsheet with 500 rows and 7 columns the last three columns have a dropdown box in each cell with a choice of 15 "cities" so the same city won't appear in more than one column in the same row but it may appear in a different column on a different row. On a seperate sheet I have a button for each of the 15 choices and need the spreadsheet to filter it if that city appears in column E, F or G when that button is pressed....if that makes sense?

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Macro Autofilter same criteria across multiple columns

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Registered User
    Join Date
    11-05-2014
    Location
    Liverpool, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Macro Autofilter same criteria across multiple columns

    Test.xlsm

    Hi,

    I want it so when you click on A in buttons sheet so that it shows all rows with an A in column E,F or G in the data sheet.

    Thanks for youe help

  6. #6
    Registered User
    Join Date
    11-05-2014
    Location
    Liverpool, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Macro Autofilter same criteria across multiple columns

    Name Address Phone Gender Filter data A Filter data B Filter data C
    xx xx xx xx A B D
    xx xx xx xx B A C
    xx xx xx xx C A K
    xx xx xx xx E B A
    xx xx xx xx D J A

    This is what it should end up like

  7. #7
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Macro Autofilter same criteria across multiple columns

    Try:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-05-2014
    Location
    Liverpool, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Macro Autofilter same criteria across multiple columns

    Hi Olly,

    Thanks so much for that; I keep getting compile error Ambiguous name detected: A and sub A() is highlighted.

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Macro Autofilter same criteria across multiple columns

    Each subroutine must have a unique name.

    Delete or rename your orignal Sub A and Sub B.

+ 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 multiple columns with multiple string criteria
    By svalentine91 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-23-2013, 11:26 AM
  2. [SOLVED] Autofilter with multiple criteria - how to ignore blank criteria
    By MOONDRIFT47 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-31-2012, 12:20 PM
  3. VBA - Retrieve List of Available Criteria on Autofilter Columns
    By Sandheep in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-14-2012, 12:02 PM
  4. Macro to Segregate the data based on Multiple Criteria's in Multiple Columns -Challenging!
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-08-2012, 07:31 AM
  5. autofilter macro: goto the next criteria down until certain criteria is reached
    By kuraitori in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-27-2008, 04:31 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