Macro Autofilter same criteria across multiple columns

[Kungfubarbie]

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.

[Olly]

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.

[Kungfubarbie]

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?

[Olly]

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

[Kungfubarbie]

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

[Kungfubarbie]

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

[Olly]

Try:

Please Login or Register  to view this content.

[Kungfubarbie]

Hi Olly,

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

[Olly]

Each subroutine must have a unique name.

Delete or rename your orignal Sub A and Sub B.