How to set multiple criteria for .advancedfilter in VBA

[dorian821]

Hello All,

I am trying to use the xlfiltercopy action with the .advancedfilter function in vba to filter through some data and copy it to another sheet. The criteria for the filter, however, will be all values between two variable values that will change on every iteration of the filter, i.e a 'continuous' data set so printing all the criteria onto a range is not possible. Consequently, as the .advancedfilter function doesn't seem to allow for criteria1, criteria2, etc.. like .autofilter, I need a way to set the criteriarange to a 'between' function, i.e. x
The code I have so far is below.

Thanks!

Dorian

Sub Filter_Report()


Dim crit1 As Single
Dim crit1lo As Range
Dim crit1hi As Range
Dim crita As Range
Dim Report As Worksheet
Dim r As Integer
Dim data As Worksheet
Dim crit1rng As Range
Dim temp As Worksheet


Workbooks("MT_Optimizer").Sheets("AAPL_Days_1").Select
Set data = Workbooks("MT_Optimizer").Sheets("AAPL_Days_1")


r = data.Cells(data.Rows.Count, "A").End(xlUp).Row
Set crita = data.Range("AY30:AY" & r)




crit1 = data.Cells(30, 51).Value
Set crit1lo = data.Cells(30, 51).Value - 0.05
Set crit1hi = data.Cells(30, 51).Value + 0.05
Set crit1rng = Union(Range("<=" & crit1hi) & Range(">=" & crit1lo))




Worksheet.Add
ActiveSheet.Name = temp


crita.AdvancedFilter Action:=xlFilterCopy, criteriarange:=crit1rng, copytorange:=temp("a1"), unique:=False




















End Sub