• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic multiple data filter based on a flexible Array

inddon

Member
Hello There,

I would like to know the actual VBA code which can dynamically filter data based on a flexible array.

I have a UserForm with a couple of CheckBoxes. Based on the selection of the CheckBox (checked/unchecked), the value required for the data filter should be added or removed from the array.

This array would be used to filter the data.

I have attached a sample workbook and explanation for your reference.

Appreciate you help. Look forward to hearing from you.

Regards,
Don
 

Attachments

RDAngelo

Member
Possibly...
Code:
Option Explicit
Private Sub CheckBoxInvalidTransaction_Click()
    CheckFilter
End Sub

Private Sub CheckBoxNoPO_Click()
    CheckFilter
End Sub

Private Sub CheckBoxOpenAmount_Click()
    CheckFilter
End Sub

Private Sub CheckBoxMatchedTransactions_Click()
    CheckFilter
End Sub

Private Sub CheckBoxShowAllTransactions_Click()
    'Remove all filters and show all rows
    Dim c As Control
    
    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" Then c.Value = False
    Next c
    Sheet1.Cells(1, 1).CurrentRegion.AutoFilter
End Sub

Private Sub CheckFilter()
    Dim c As Control, v() As String
    Dim i As Long, rg As Range, b As Boolean

    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" Then
            i = i + 1
            If c.Value = True Then
                ReDim Preserve v(1 To i)
                v(i) = i
                b = True
            End If
        End If
    Next c
    Set rg = Sheet1.Cells(1, 1).CurrentRegion
    If b = False Then
        rg.AutoFilter
        Exit Sub
    End If
    rg.AutoFilter 1, Array(v), xlFilterValues
End Sub
 
Last edited:

inddon

Member
Possibly...
For Each c In Me.Controls
If TypeName(c) = "CheckBox" Then
i = i + 1
If c.Value = True Then
ReDim Preserve v(1 To i)
v(i) = i
b = True
End If
End If
Next c
Set rg = Sheet1.Cells(1, 1).CurrentRegion
If b = False Then
rg.AutoFilter
Exit Sub
End If
rg.AutoFilter 1, Array(v), xlFilterValues
End Sub
[/CODE]

Thank you RDAngelo
Your solution works perfect.

Regards,
Don
 
Top