Filter my data using cell value.

[omairhe]

Hello Gurus,

How do I filter my rows without using autofilter and instead using cell value in "I3"

I would like to filter column G with an exact match.

So if column G contains "cat" and I3 is also "cat", then give all rows with "cat" inside column G.

Will appreciate a lot.

 

[jasonb75]

You could use an advanced filter that refers to I3 as the criteria, the link below provides a detailed introduction and guide to advanced filters.

Filter by using advanced criteria - Microsoft Support

If the Excel data you want to filter requires complex criteria you can filter by using advaned criteria.

support.microsoft.com

Other than that you will need to use VBA.

 

[omairhe]

You could use an advanced filter that refers to I3 as the criteria, the link below provides a detailed introduction and guide to advanced filters.

Filter by using advanced criteria - Microsoft Support

If the Excel data you want to filter requires complex criteria you can filter by using advaned criteria.

support.microsoft.com

Other than that you will need to use VBA.

Thank you Jason. I'll see how it goes.

 

[omairhe]

I would like to use advanced filter rather than use normal Autofilter. But I have many sheets with me for which I don't want to repeat and apply advance filter for each sheets. My sheets are all identical and my criteria will always be constant which is located in sheet named "HSheet" cell range "I3"
With choosing the "filter the list in place" option, would it be possible to now make the "List range" dynamiic and to start from A6 of my active sheet up until last data cell from below?

I have found this macro from other forum. But the range is fixed. How can I adjust this code to my requirement please?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5:D21").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("I2:I3"), Unique:=False
End Sub

 

[jasonb75]

I don't have anything to hand to test the theory on, maybe this will work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5:D6").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("I2:I3"), Unique:=False
End Sub

 

[omairhe]

I don't have anything to hand to test the theory on, maybe this will work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5:D6").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("I2:I3"), Unique:=False
End Sub

1. Is it possible to have an exact match? for instance "the letter c will give results for all words that have 'c' in it. whereas a word typed in full should give the resulting filtered rows only
2. Also I would like to avoid the CurrentRegion and instead use the last cell from below script, as my data have blank rows in between.

Other than these two your code will work. Need further assistance.

 

[jasonb75]

This should fix point 2 in your post

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A5:D" & lRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("I4:I5"), Unique:=False
End Sub

For point 1, the criteria needs to be specified as equal, which means that you need to join an = symbol to the start of the criteria in the cell. For this purpose, it might be better to have a second pair of cells for the criteria with simpler formulas.

Assuming I4 and I5 used for this, (code above edited to suit).
In I4, =I2
In I5, ="="&I3

Then it should work as required.

 

[omairhe]

This should fix point 2 in your post

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A5:D" & lRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("I4:I5"), Unique:=False
End Sub

For point 1, the criteria needs to be specified as equal, which means that you need to join an = symbol to the start of the criteria in the cell. For this purpose, it might be better to have a second pair of cells for the criteria with simpler formulas.

Assuming I4 and I5 used for this, (code above edited to suit).
In I4, =I2
In I5, ="="&I3

Then it should work as required.

This works Gr8. Thanks.

 

[najamabbas]

Hello Guys, I have the following code working perfectly on the same worksheet.

However, Range("D3") value comes from another sheet(Introduction) tab so when the value changes in D3, it doesn't get auto filtered unless I click double click on it. I would like this to get auto-update as soon as the value changes. Hope it makes sense.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("D3").Address Then
Range("A10:C250").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("D2:D3")
End If
End Sub

Any help would be highly appreciated.