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]

Ok, how about

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

 

[Westbury]

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]

Oops, got the arrays wrong, try

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]

stepping through the code I get a Runtime 424 Object required on line

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

 

[Fluff]

Not sure what I was thinking, try

    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]

We moved down one line . There's a Runtime error 438 at

.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

 

[Westbury]

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

 

[Fluff]

Is the data already filtered when you run the code?
Also is your data in a structured table, or just a range?

 

[Westbury]

I've just noticed that between post #5 & #11 the col ref changed from 11 to 10 . I've editted it to

.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]

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?