Help with VBA Worksheet Event Code;

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Good morning,
I'm not very familiar with how to add more information to this code than below. It works perfectly fine if I wanted to filter a range based on a single cell's value.
I want to have 4 different cells and have the table filter based on all 4 values at the same time.

Filter column# 4 based on value at F2
then Filter column# 6 based on value at F3
then Filter column# 5 based on value at F4
then Filter column# 7 based on value at F5
Whatever's left is what I want to see on the table.

Also, if any of the values don't exist, I assume the table just goes blank, which is fine. If I want to see all data with no filters at all, is there a way to just set each value at F2, F3, F4, and F5 to "All" for it to show the entire table?

The current code is all I have below and it works fine with just one value, but I don't know how to expand it to include 4 values at the same time.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$2" Then
If Range("F2") = "All" Then
  Range("A13").AutoFilter
Else
  Range("A13").AutoFilter Field:=4, Criteria1:=Range("F2")
End If
End If
End Sub
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
this assumes that the trigger remains only F2
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$2" Then
        With Range("A13")
            If Range("F2") = "All" Then
                .AutoFilter
            Else
                .AutoFilter Field:=4, Criteria1:=Range("F2")
                .AutoFilter Field:=6, Criteria1:=Range("F3")
                .AutoFilter Field:=5, Criteria1:=Range("F4")
                .AutoFilter Field:=7, Criteria1:=Range("F5")
            End If
        End With
    End If
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
this is triggered if any of the values F2:F5 are amended
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F2:F5")) Is Nothing Then
        With Range("A13")
            If Range("F2") = "All" Then
                .AutoFilter
            Else
                .AutoFilter Field:=4, Criteria1:=Range("F2")
                .AutoFilter Field:=6, Criteria1:=Range("F3")
                .AutoFilter Field:=5, Criteria1:=Range("F4")
                .AutoFilter Field:=7, Criteria1:=Range("F5")
            End If
        End With
    End If
End Sub
 

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Thank you for help!
When I use that code, it filters correctly for a moment (based on the first cell's value, F2), then filters everything away completely and shows nothing. By the time I enter a value in F2, the whole table has filtered blank. If I change the values then in F3, F4, or F5, nothing changes. It stays blank.

I have a data validation list at each cell, F2, F3, F4, and F5. When I select the first value at F2, like I said it'll filter that data but then filter everything blank. Any ideas?

Cell F2 is Text, F3 is a Number, F4 is a Date, F5 is Text - not formatted as such individually, but that's what each value would represent when selected.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F2:F5")) Is Nothing Then
        With Range("A13")
            .AutoFilter
            If Range("F2") <> "All" Then
                .AutoFilter Field:=4, Criteria1:=Range("F2")
                .AutoFilter Field:=6, Criteria1:=Range("F3")
                .AutoFilter Field:=5, Criteria1:=Range("F4")
                .AutoFilter Field:=7, Criteria1:=Range("F5")
            End If
        End With
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,128,153
Messages
5,629,000
Members
416,358
Latest member
grsaltzman

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