VBA Worksheet Change Event Multi Filter

R

roykana

Active Member
Joined
Mar 8, 2018
Messages
311
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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
 
Upvote 0
EXCEL MAX said:
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
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
Fluff said:
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
Click to expand...
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
 
Upvote 0
No need, just delete O1 & P1 separately & select ALL from N1
 
Upvote 0
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
 
Upvote 0
Solution
Fluff said:
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
Click to expand...
Fluff said:
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
Click to expand...
Dear Mr. Fluff,

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

Thanks
roykana
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Removing Autofilter Without Resetting All Filters
Replies
3
Views
169
RoryA
RoryA
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
478
Alex Blakenburg
A
A
  • Question
Worksheet Change Event Triggering With Cell Validation "Cancel"
Replies
1
Views
217
rlv01
R
F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
185
Domenic
D
S
  • Question
VBA To Filter Conditionally Formatted coloured cells in multiple columns
Replies
1
Views
137
bobsan42
bobsan42

Forum statistics

Threads
1,214,793
Messages
6,121,617
Members
449,039
Latest member
Mbone Mathonsi

Share this page

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
Back
Top