Filter across multiple columns, but without using helper column

[ellison]

Hi, I'm trying to filter for a single criteria across multiple columns.

- I'd normally use a helper column to concatenate the relevant fields together.
- Then copy / paste special / paste the "values" into the same helper column in order that they can be filtered.
- For example

Row	Model	Material	Colour	Helper
2	XYZ789	Wood	Silver	XYZ789;Wood;Silver
3	ABC123-4	Plastic	Black	ABC123-4;Plastic;Black
4	14CV56576		Wood	14CV56576;Wood

EG If we wanted to view any row of info which contained "Wood", then the filter would show Rows 2 and 4.

Is there a way of doing this where I can make this work without having to copy / paste special / paste "values in the helper column? (otherwise my fat fingers seem to be causing all sorts of errors!).

Huge thanks

 

[ellison]

I think I've got something that will work...
- I've got data in Columns A to C and a textjoin helper column in Cell D.
- The criteria I actually want to search for is in H1.
- NB H1 will EQUAL the concat column, so need to put in wildcards if need it to be contains
- Then the code that seems to be doing the job is:

Sub AutoFilter_in_Excel_Multiple_Col_Filter()

Range("A1:F10").AutoFilter Field:=4, Criteria1:=Range("H1").Value

End Sub


Best of luck

 

[Michael M]

The foirst code will find any text from H1 regardless of Case !
The loop starts at row 10 and wotks backwards to row 2
The second code UNHIDES all rows
I don't understand why you don't simply put a couple of shapes on the worksheet and assisgn the macros to them....so click the 1st shape to find the criteria....once you have done what you need to do with the data click the 2nd shape to unhide the rows.
I don't understand why you would want to use it as a function

Option Compare Text
Sub MM1()
Dim r As Long, rng As Range
Set rng = Cells(1, 8)
For r = 10 To 2 Step -1
    If Cells(r, 2).Value = rng Or Cells(r, 3) = rng Then
        Rows(r).EntireRow.Hidden = False
        Else
        Rows(r).EntireRow.Hidden = True
    End If
Next r
End Sub



Sub MM2()
 Cells.EntireRow.Hidden = False
End Sub

 

[ellison]

Thanks very much Michael, running much better now!