Search text in multiple columns and return all rows

[maggsy]

I have a worksheet where users can search for all suppliers of a certain item. At the moment they enter their search term in a dialogue box and I then use the advanced filter funtionality to show all rows containing that value. (code below)
However I need to be able to search in more than one column, is there a way to do this?
e.g.
Column A Column B Column C
Supplier 1 - Electrical pumps - inline
Supplier 2 - Impellors - pump spares
Suppplier 3 - valves - hydraulic

If user was to enter 'pump' rows 2 and 3 need to be shown, currently it will only show row 2 as it is filtering on column 2

Any help or sugggestions would be much appreciated


Sub Find_cat()
'
' Find Macro

Dim catvalue
Application.ScreenUpdating = False

catvalue = InputBox("Please enter the item you are searching for", "Category Search")
Sheets("Table").Select
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("A:s").AutoFilter

ActiveSheet.Range("A$1:$S$987").AutoFilter Field:=8, Criteria1:="*" & catvalue & "*"
End Sub

 

[markgordonis]

Hi

Which version of Excel are you using?

Thanks
M

 

[markgordonis]

Hi

If using Excel 2007/Excel 2010, the following works for me (although it's not using VBA code):

First, have the following in column D and copy down:
=concatenate(B1,C1)

Then, put on a filter, click on the filter, then "text filters", "contains", and then enter the desired string (e.g. pump)

M

 

[maggsy]

Great, that has solved it - the simple solutions are always the best