Autofilter and checkboxes

[Westbury]

I want to use a a groups of checkboxes in conjunction with the autofilter function in Excel. I can see plenty of examples of code to set up the autofilter where the criteria are being written into a worksheet but nothing similar to my needs.

I have a group of 9 checkboxes to be used against column K and a couple more groups on other columns. In each group any one or any number of the checkboxes can be selected.

Can someone give me a steer on dealing with this variable number of criteria in VBA please?

Thanks
Geoff

 

[Fluff]

Thinking on this, if you have "3000 to 5000" in M22 then there is no way you can use that in an autofilter.

 

[Westbury]

Your points

When using arrays you can only filter on exact values.------Yes, this is fine with filtering cols F & K where it's an item description
Will you only select one checkbox for col M?-----It could be more than one, this parameter is effectively size or length
Also will you always select at least one checkbox from each column?--the data in F & K could be filtered on one or more types; yuor code does this.

I've just used the built in macro recorder on a spare data sheet to filter col E for a range of sizes

Selection.AutoFilter
    ActiveSheet.Range("$A$1:$K$4575").AutoFilter Field:=5, Criteria1:=">=5000" _
        , Operator:=xlAnd, Criteria2:="<=15000"

This works so I'll go away and think how I can restructure the sheet with the checkboxes and the relevant code.....

 

[Westbury]

For anyone who needs a similar solution ....

I've set up 3 routines to capture >=, <= , and values between min & max

the <= is as follows [the code for >= is identical but it reads the >= operator from a different cell]

Dim Op As String Dim Value As Long

Op = Worksheets("Start sheet").Range("N23").Value
Value = Worksheets("Start sheet").Range("O23").Value

Worksheets("Data").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$K$4575").AutoFilter Field:=5, Criteria1:=Op & Value _
        , Operator:=xlAnd

the routine for filtering between 2 values is

Dim ValueMin, ValueMax  As Long
ValueMin = Worksheets("Start sheet").Range("N21").Value
ValueMax = Worksheets("Start sheet").Range("O21").Value
Worksheets("Data").Select

    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$K$4575").AutoFilter Field:=5, Criteria1:=">=" & ValueMin _
        , Operator:=xlAnd, Criteria2:="<=" & ValueMax

I've set up a table in the spreadsheet where the user can enter the min, max, upper or lower values and assigned option buttons so that only one is run at a time. All works OK except sometimes there's runtime error on the line

Selection.AutoFilter

This seems to be something to do with clearing the filter from previous runs.


Fluff, thank you for your interest in my challenge and persevering with me. I've learnt a lot here!

Geoff

 

[Fluff]

Glad you sorted it & thanks for the feedback.