Hi all
I have the following code which works brilliantly for one criteria, but I want to add another, can you please help?
I want to add another control trigger (Set rngTrigger = Worksheets("Formulas").Range("H1"))
Code
Option Explicit
Sub RunFilter(newVal As String)
Dim rngData As Range
Dim rngCrit As Range
Dim rngTrigger As Range
'Where is the criteria?
Set rngCrit = Worksheets("Formulas").Range("CritRange")
'Where is data?
Set rngData = Worksheets("OB").UsedRange
'Cell we use to control criteria
Set rngTrigger = Worksheets("Formulas").Range("H1")
On Error GoTo cleanUp
with application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
end with
rngTrigger.Value = newVal
'Calculate new criteria
rngCrit.Calculate
rngData.AdvancedFilter xlFilterInPlace, rngCrit
Application.Goto rngData.Cells(1), Scroll:=True
cleanUp:
with application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
end with
End Sub