VBA Autofilter: Filter Data based on a two cell's values?

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Hello,
I have the below code as a worksheet event code in my Worksheet titled "Data". Is there a way to edit this code to filter by two criteria, and not just one? I want to filter column 8 based on the criteria at G2 and H2. Is this possible?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then
If Range("G2") = "All" Then
  Range("A5").AutoFilter
Else
  Range("A5").AutoFilter Field:=8, Criteria1:=Range("G2")
End If
End If
End Sub

Sorry for the additional edit; but in addition, I have 4 different columns to sort, not just column 8. Can I just use this same code 4 times, one for each column, or is there an easier way/elegant way of doing that?

For example, I want column 5 to be filtered based on cell B1, column 6 to be filtered based on cell C1, column 7 to be filtered based on D1 & E1, and column 8 to be filtered based on F1 & G1. Is this possible?
 
Last edited:

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
for first par of your query
try
Range("A5").AutoFilter Field:=8, Criteria1:=Range("G2"), Operator:=xlAnd, Criteria2:=rang("h2")

for the later part, use autofilter statement multiple time with different criterion, in sequence as described in your query.

hth...
 

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
HI there, thanks for the suggestion! However, it's not performing actually what I'm looking for. I want the table to be filtered by showing me both criteria in G2 and H2. What appears to be happening using your suggestion is that it's first filtering to show me ONLY values equal to G2, then look for any values in that filtered list that match H2, but that's not ever going to be possible here. Those cells, in this instance, are dates. I want to see two dates at the same time, not filter one then the other out. Does that makes sense?

Edit: Sorry, I'm also super new to VBA. How would I use the autofilter statement multiple times?
 
Last edited:

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
This is basically what I would need to happen;

Filter column 4 based on the value of cell G2,
Filter column 22 based on the values of cell G3 and H3 at the same time or one at a time. (Sometimes just filter based on Cell G3, sometimes both G3 and H3)
Filter column 32 based on the value of cell G4
Filter column 25 based on the value of cell G5
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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