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<criteriarange<y. how="" can="" i="" do="" this?=""

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

 

[RoryA]

The criteria for advanced filter has to be a range. There is no way round that.

I'm not really sure why you can't simply populate a couple of cells with the upper and lower values or even use an autofilter. (unfortunately your code doesn't make a lot of sense so I can't really tell which field you're trying to filter on.)

 

[dorian821]

The criteria for advanced filter has to be a range. There is no way round that.

I'm not really sure why you can't simply populate a couple of cells with the upper and lower values or even use an autofilter. (unfortunately your code doesn't make a lot of sense so I can't really tell which field you're trying to filter on.)

Thanks for your reply. The code is a first sketch at this filter/copy part of the program. Understandable that it doesn't make much sense, I apologize for any difficulty that may cause.

The basic issue is that the data that will be filtered has numbers with up to 4 decimals and I need to include all numbers within a certain value range. If I have to populate a range of cells with all possible values within that value range then I will have to generate a lot of values for every iteration of the program, which I expect will slow down the execution, especially considering that it will perform several tens of thousands of iterations.

I can do it with autofilter, but I was thinking that the xlcopy... function of advancedfilter would be more expedient. If I can just input the extremes of the value range, however, as you have suggested, then that would work fine. I just didn't know if or how that was possible. Do I simply set two cells to the extreme values and then set those two cells as the criteriarange?

 

[RoryA]

You'd repeat the column header twice (in two adjacent cells), then in the cells below enter >1 and <10 for example. Then use those 4 cells as the criteria range.