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,344
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Westbury()
   Dim JAry(1 To 9) As Variant, MAry(1 To 4) As Variant, PAry(1 To 5) As Variant
   Dim i As Long
   For i = 1 To 9
      If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = "True" Then
         JAry(i) = Cells(i + 20, 10)
      End If
    Next i
    For i = 10 To 13
      If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = "True" Then
         MAry(i) = Cells(i + 11, 13)
      End If
    Next i
    For i = 14 To 18
      If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = "True" Then
         PAry(i) = Cells(i + 7, 16)
      End If
    Next i
    With ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion
      .Range("A1").CurrentRegion.AutoFilter 10, JAry, xlFilterValues
      .Range("A1").CurrentRegion.AutoFilter 5, MAry, xlFilterValues
      .Range("A1").CurrentRegion.AutoFilter 6, PAry, xlFilterValues
      .AutoFilter.Range.Offset(1).Copy Sheets("Output").Range("A2")
      .AutoFilterMode = False
   End With
      
End Sub
Change the output sheet & range to suit
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
The logic behind the code is what counts; genius.

Jary works ok. I can see it in the Locals window but I'm getting a Runtime error 9 Subscript out of range with both the Mary & Pary

The cell references are correct for Mary & Pary. The cells in col M contain numbers and are formatted Numbers. The cells in P are text and are formatted General, like col J.

What could be the cause of this problem?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,344
Office Version
  1. 365
Platform
  1. Windows
Oops, got the arrays wrong, try
VBA Code:
Sub Westbury()
   Dim JAry(1 To 9) As Variant, MAry(10 To 13) As Variant, PAry(14 To 18) As Variant
   Dim i As Long
   For i = 1 To 9
      If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = "True" Then
         JAry(i) = Cells(i + 20, 10)
      End If
    Next i
    For i = 10 To 13
      If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = "True" Then
         MAry(i) = Cells(i + 11, 13)
      End If
    Next i
    For i = 14 To 18
      If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = "True" Then
         PAry(i) = Cells(i + 7, 16)
      End If
    Next i
    With ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion
      .Range("A1").CurrentRegion.AutoFilter 10, JAry, xlFilterValues
      .Range("A1").CurrentRegion.AutoFilter 5, MAry, xlFilterValues
      .Range("A1").CurrentRegion.AutoFilter 6, PAry, xlFilterValues
      .AutoFilter.Range.Offset(1).Copy Sheets("Output").Range("A2")
      .AutoFilterMode = False
   End With
End Sub
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
stepping through the code I get a Runtime 424 Object required on line

VBA Code:
.AutoFilter.Range.Offset(1).Copy Sheets("Output").Range("A2")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not sure what I was thinking, try
VBA Code:
    With ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion
      .AutoFilter 10, JAry, xlFilterValues
      .AutoFilter 5, MAry, xlFilterValues
      .AutoFilter 6, PAry, xlFilterValues
      .Worksheet.AutoFilter.Range.Offset(1).Copy Sheets("sheet1").Range("A2")
      .Worksheet.AutoFilterMode = False
   End With
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
We moved down one line :oops:. There's a Runtime error 438 at

VBA Code:
.Worksheet.AutoFilterMode = False

The filters are applied to the data sheet but as I step through the code the header row only is visible; the next row being the first empty row below the data & the remaining rows empty.
There's nothing copied to sheet1
 
Last edited:

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136

ADVERTISEMENT

and when I click on the data sheet filter dropdowns there's only "select", the other lables have disappeared
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,344
Office Version
  1. 365
Platform
  1. Windows
Is the data already filtered when you run the code?
Also is your data in a structured table, or just a range?
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
I've just noticed that between post #5 & #11 the col ref changed from 11 to 10 . I've editted it to

VBA Code:
.AutoFilter 11, JAry, xlFilterValues
so this works ok.

Col E values are numbers and I've currently got cell M21 as >5000, cell M22 3000 to 5000, cell M23 1000 to 3000 &, cell M24 <1000. 'Mary' is picking these up but the format of these values isn't making sense to the filter in col 5 and the code trips up with runtime error 1004 Autofilter method of range class failed.

The data is just a range.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,344
Office Version
  1. 365
Platform
  1. Windows
When using arrays you can only filter on exact values.
Will you only select one checkbox for col M?
Also will you always select at least one checkbox from each column?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,451
Messages
5,601,727
Members
414,470
Latest member
glukemey

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