Possible to filter for cell value to be between to given values

[mehaffey]

I have inherited an Excel workbook that has several pages with buttons for macros to filter the data on various sets of criteria. This is the specific code I am working with below and the entire workbook follows the same pattern.
The head of the specific module and the filter code look like this:

Public Function SetupVariant() As Collection
    Dim allFilters As Collection, Filter As FilterDefinition
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Variants")
    ' create a main list of filters
    Set allFilters = New Collection

' Test filter
    Set Filter = New FilterDefinition
    Filter.Init "Variants", "Candidates", "Alt Reads > 10, 0.3 < VAF < 0.97"
    Filter.AddCriteria "Alt Reads", ">10"
    Filter.AddCriteria "VAF", Array(">0.03","<0.97")
    allFilters.Add Filter

Obviously the code for AddCriteria Array for >0.03 and <0.97 only shows rows for the <0.97 condition. Is there a way/syntax for Filter.AddCriteria that will do this AND/between logic?

Thank you for your time!

 

[kevin9999]

There's possibly an easier way to go about what you're doing, and we only get to see part of your code & none of your data layout, so the following is just a quick demo of how you would get a 'between' result using Autofilter instead:

Sub Test_Filter()
    With [A1].CurrentRegion
        .AutoFilter 1, ">0.03", xlAnd, "<0.97"
    End With
End Sub

before:

Book1
	A	B
1	HDR1	HDR2
2	0.11	data
3	0.98	data
4	0.20	data
5	0.07	data
6	0.96	data
7	0.13	data
8	0.01	data
9	0.31	data
10	0.50	data
11	1.00	data
12
Sheet1

after:

Book1
	A	B
1	HDR1	HDR2
2	0.11	data
4	0.20	data
5	0.07	data
6	0.96	data
7	0.13	data
9	0.31	data
10	0.50	data
12
Sheet1