How to set multiple criteria for .advancedfilter in VBA

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

Dorian

It is possible to set a 'between' criteria for advanced filter in a range.

In the range you would repeat the field twice in the header row and below you would have your ">=" & crit1lo and "<=" & crit1hi.

Try doing it manually to see what I mean.

PS Can you add code tags when posting code?

Thanks, What do you mean repeat the field twice in the header row?

I've tried what I think you are suggesting, and am receiving the following error at the line for the advanced filter


Method 'range' of object '_Global' failed

Sub Filter_Report()

Dim crit1           As Single
Dim crit1lo         As Single
Dim crit1hi         As Single
Dim crita           As Range
Dim report          As Worksheet
Dim r               As Integer
Dim rr              As Integer
Dim rt               As Integer
Dim data            As Worksheet
Dim data1           As Range
Dim crit1rng        As Range
Dim temp            As Worksheet
Dim s               As Integer

Dim rate            As Range
Dim symbol          As String


Set temp = Workbooks("MT_Optimizer").Sheets("Temp_Report")
Set report = Workbooks("MT_Optimizer").Sheets("Report")
Workbooks("MT_Optimizer").Sheets("AAPL_Days_1").Select
Set data = Workbooks("MT_Optimizer").Sheets("AAPL_Days_1")
Set rate = Workbooks("MT_Optimizer").Sheets("Temp_Report").Range("G1:R3")


s = 4
r = data.Cells(data.Rows.Count, "A").End(xlUp).Row
rr = report.Cells(data.Rows.Count, "A").End(xlUp).Row
rt = temp.Cells(data.Rows.Count, "A").End(xlUp).Row



crit1 = data.Cells(8400, 51).Value
crit1lo = crit1 - 0.05
crit1hi = crit1 + 0.05



   
    
    'If ActiveSheet.AutoFilterMode Then
        'ActiveSheet.ShowAllData
    'End If
    
        
    data.Range("A4:BO" & r).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(">=" & crit1lo & "<=" & crit1hi), _
        copytorange:=temp.Range("A5")
    
    'Rows(s + 1).Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.Copy
    
    'temp.Activate
    'temp.Range("A5").Select
    'temp.Paste
    temp.Calculate
    temp.Range("G2:AP2").Select
    Selection.Copy
    
    report.Select
    rr = report.Cells(data.Rows.Count, "A").End(xlUp).Row
   
    report.Cells(rr + 1, 10).PasteSpecial xlPasteValues
    
    report.Cells(rr + 1, 2).Value = crit1
    
end sub

The criteria range needs to be on a worksheet, you can't 'create' it in the code.

You can however populate the criteria range using code.