VBA Worksheet Change Event Multi Filter

roykana

Board Regular
Joined
Mar 8, 2018
Messages
113
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
295
Office Version
  1. 2007
Platform
  1. Windows
Hello Roykana,
I have done some modification.
It should work now.
VBA Code:
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

Board Regular
Joined
Mar 8, 2018
Messages
113
Office Version
  1. 2010
Platform
  1. Windows
Hello Roykana,
I have done some modification.
It should work now.
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Board Regular
Joined
Mar 8, 2018
Messages
113
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
No need, just delete O1 & P1 separately & select ALL from N1
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
113
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Ok, try
VBA Code:
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
 
Solution

roykana

Board Regular
Joined
Mar 8, 2018
Messages
113
Office Version
  1. 2010
Platform
  1. Windows
Ok, try
VBA Code:
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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

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