Filtering Columns (without using the filter function!)

[ellison]

Hi, the code below (that Peter posted) so that we can use a "multi-filter" on our spreadsheets has been BRILLIANTLY helpful.

In short, it filters all of the data on a given spreadsheet for whatever search strings are placed in Row 1.
So at the moment we could filter Column A for "red", Column B for "plastic" and Column C for "rounded".

But what we would like to do is add in multiple options on our filters... like an "OR" option
i.e. search Column A for "red" OR "yellow" or "blue" and then Column b for "plastic" and Column C for "rounded"

(We are on a mission to clean up our data and collating all the different reports was like pulling teeth. Using Peter's code has been superb and tweaking it to include this feature would be the cherry on the cake!)

Best

Neil



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range
Dim FilterRangeFirstCol As Long, FieldCol As Long
Dim sCrit As Variant

If ActiveSheet.AutoFilterMode Then
With ActiveSheet.AutoFilter.Range
Set Changed = Intersect(Target, .EntireColumn, Rows(1))
If Not Changed Is Nothing Then
FilterRangeFirstCol = .Column
For Each c In Changed
FieldCol = c.Column - FilterRangeFirstCol + 1
If IsEmpty(c.Value) Then
.AutoFilter Field:=FieldCol
Else
If IsNumeric(c.Value) Then
sCrit = c.Value
Else
sCrit = "*" & c.Value & "*"
End If
.AutoFilter Field:=FieldCol, Criteria1:=sCrit
End If
Next c
End If
End With
End If
End Sub