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

[WSBirch]

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?

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?

 

[fadee2]

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]

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?

 

[WSBirch]

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