Autofilter and checkboxes

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
Thinking on this, if you have "3000 to 5000" in M22 then there is no way you can use that in an autofilter.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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

VBA Code:
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

Board Regular
Joined
Jun 7, 2009
Messages
136
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]

VBA Code:
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

VBA Code:
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

VBA Code:
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top