VBA Worksheet Change Event Multi Filter

[roykana]

Dear All master,
I want multi filters. In cell N1, the filter validation by year, cell O1 filter validation by period,

cell P1 filter validation by category. So please modify the code vba I have.
This is my link : VBA Worksheet Change Event Multi Filter.xlsm
file

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$1" Then
    If Range("N1") = "ALL" Then
        Range("A2").AutoFilter
    Else
        Range("A2").AutoFilter Field:=4, Criteria1:=Range("N1")
    End If
End If
End Sub

Thanks
roykana

 

[EXCEL MAX]

Hello Roykana,
I have done some modification.
It should work now.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$N$1" And Not Range("N1").Value = "" Then
        If Range("N1") = "ALL" Then
            Range("A2").AutoFilter
        Else
            If Range("O1") <> 0 Then
                Range("A2:A1385").AutoFilter Field:=4, Criteria1:=Range("N1")
                Range("A2:A1385").AutoFilter Field:=5, Criteria1:=Range("O1")
            Else
                Range("A2").AutoFilter
                Range("A2:A1385").AutoFilter Field:=4, Criteria1:=Range("N1")
            End If
        End If
        Exit Sub
    End If
    
    If Target.Address = "$O$1" And Not Range("O1").Value = "" Then
        If Range("N1") = "ALL" Then
            Range("A2").AutoFilter
            Range("A2:A1385").AutoFilter Field:=5, Criteria1:=Range("O1")
        Else
            Range("A2:A1385").AutoFilter Field:=4, Criteria1:=Range("N1")
            Range("A2:A1385").AutoFilter Field:=5, Criteria1:=Range("O1")
        End If
    End If
    
End Sub

 

[roykana]

Hello Roykana,
I have done some modification.
It should work now.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$N$1" And Not Range("N1").Value = "" Then
        If Range("N1") = "ALL" Then
            Range("A2").AutoFilter
        Else
            If Range("O1") <> 0 Then
                Range("A2:A1385").AutoFilter Field:=4, Criteria1:=Range("N1")
                Range("A2:A1385").AutoFilter Field:=5, Criteria1:=Range("O1")
            Else
                Range("A2").AutoFilter
                Range("A2:A1385").AutoFilter Field:=4, Criteria1:=Range("N1")
            End If
        End If
        Exit Sub
    End If
   
    If Target.Address = "$O$1" And Not Range("O1").Value = "" Then
        If Range("N1") = "ALL" Then
            Range("A2").AutoFilter
            Range("A2:A1385").AutoFilter Field:=5, Criteria1:=Range("O1")
        Else
            Range("A2:A1385").AutoFilter Field:=4, Criteria1:=Range("N1")
            Range("A2:A1385").AutoFilter Field:=5, Criteria1:=Range("O1")
        End If
    End If
   
End Sub

Dear sir,

I did a validation filter in cell p1 there was no change. Can you change the code "Range (" A2: A1385 ")" because the number of rows or records can be larger than the sample file I attached so that it can be used more easily without having to change the cell range.

thanks
roykana

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("N1:P1")) Is Nothing Then
      If Target.Value = "ALL" Or Target.Value = "" Then
         Range("A1").AutoFilter Target.Column - 10
      Else
         Range("A1").AutoFilter Target.Column - 10, Target.Value
      End If
   End If
End Sub

 

[roykana]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("N1:P1")) Is Nothing Then
      If Target.Value = "ALL" Or Target.Value = "" Then
         Range("A1").AutoFilter Target.Column - 10
      Else
         Range("A1").AutoFilter Target.Column - 10, Target.Value
      End If
   End If
End Sub

Dear mr. Fluff

Thank you very much.

I added validation in cell N1, which is called "clear". Then it means clear all filters. How about the final vba code ?

Thanks
roykana

 

[Fluff]

No need, just delete O1 & P1 separately & select ALL from N1

 

[roykana]

No need, just delete O1 & P1 separately & select ALL from N1

I think it's simpler to make VBA code, namely clear all filters in the module

 

[Fluff]

Ok, try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("N1:P1")) Is Nothing Then
      If Target.Value = "ALL" Or Target.Value = "" Then
         Range("A1").AutoFilter Target.Column - 10
      ElseIf Target.Value = "CLEAR" Then
         Range("A1").AutoFilter
      Else
         Range("A1").AutoFilter Target.Column - 10, Target.Value
      End If
   End If
End Sub

 

[roykana]

Ok, try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("N1:P1")) Is Nothing Then
      If Target.Value = "ALL" Or Target.Value = "" Then
         Range("A1").AutoFilter Target.Column - 10
      ElseIf Target.Value = "CLEAR" Then
         Range("A1").AutoFilter
      Else
         Range("A1").AutoFilter Target.Column - 10, Target.Value
      End If
   End If
End Sub

Ok, try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("N1:P1")) Is Nothing Then
      If Target.Value = "ALL" Or Target.Value = "" Then
         Range("A1").AutoFilter Target.Column - 10
      ElseIf Target.Value = "CLEAR" Then
         Range("A1").AutoFilter
      Else
         Range("A1").AutoFilter Target.Column - 10, Target.Value
      End If
   End If
End Sub

Dear Mr. Fluff,

Thank you very much. the code you provide works perfectly.

Thanks
roykana

 

[Fluff]

You're welcome & thanks for the feedback.