Filter based on cell value

[ppleasebob]

Board,

I want to filter a range (A18:Q2300) based on the content of cell H4 (for column A) and cell h6 (for column B).

I have spent prpbably the whole morning going through the archive looking for an answer and I know I gonna scream when one of you kind folk answer this for me.

Thanks for looking

Bob

 

[Yard]

Check out the Advanced Filter functionality, and use H4 and H6 as part of your Criteria range.

 

[Jon von der Heyden]

If you are looking for a VBA solution, perhaps:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DoFilter()<br><br><SPAN style="color:#00007F">Dim</SPAN> rCrit1 <SPAN style="color:#00007F">As</SPAN> Range, rCrit2 <SPAN style="color:#00007F">As</SPAN> Range, rRng1 <SPAN style="color:#00007F">As</SPAN> Range, rRng2 <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">With</SPAN> Application<br>    .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> rCrit1 = Range("H4")<br><SPAN style="color:#00007F">Set</SPAN> rCrit2 = Range("H6")<br><br><SPAN style="color:#00007F">Set</SPAN> rRng1 = Range("A18:Q2300")<br><SPAN style="color:#00007F">Set</SPAN> rRng2 = Range("A19:Q2300")<br><br><SPAN style="color:#00007F">With</SPAN> rRng1<br>    .AutoFilter field:=1, Criteria1:=rCrit1.Value, Operator:=xlOr<br>    .AutoFilter field:=2, Criteria1:=rCrit2.Value<br>    rRng2.SpecialCells(xlCellTypeVisible).EntireRow.Copy <SPAN style="color:#007F00">'or do something else</SPAN><br>    .AutoFilter<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Domski]

Like this:

Sub Filter_Stuff()
 
    With Sheets("Sheet1")
 
    .Range("A18:Q2300").AutoFilter Field:=1, Criteria1:=.Range("H4").Value, Field:=2, Criteria1:=.Range("H6").Value
 
    End With
 
End Sub

Hope it helps,

Dom

 

[ppleasebob]

Thanks for the responses,
Yard - I'm struggling to see how I can put the two cell references as the criteria, would I use $h$4 & $h$6 or commas - how would the column A be filtered on H4 and column B filtered on H6


Jon - I get a runtime error message - Application-Defined or object-defined error

I did tinker with it a little (changing the range for rRang2 to be A18) and managed to get an error message saying 'That command cannot be used on multiple selections'

Domski - I get asked to select a cell within the range, I managed to get it to show the content of H6 in the filter box for Column A, but it didn't seem to want to hold a value in Column B

I'm not that used to VB so it may be something very basic..

Await any further advices that you may have..

 

[Yard]

Yard - I'm struggling to see how I can put the two cell references as the criteria, would I use $h$4 & $h$6 or commas - how would the column A be filtered on H4 and column B filtered on H6

I assume your data has column headers, in which case you just need to have H3 = "ColumnAHeader" and H5 = "ColumnBHeader" and use H3:H6 as your Criteria.

Oh, hold on, you might need to move H5 and H6 to I3 and I4. The Criteria selection itself is actually a bit rubbish (IMHO), but the Advanced Filter is a good function.

 

[Jon von der Heyden]

Hi Bob

Post the tinkered code that you used. I built this on my machine, an attempt to replicate your needs; and it works ok so I wonder if you have made a typo. For starters you need to be consistent, I used rRng1 and rRng2, you have mentioned rRang2.

 

[ppleasebob]

Jon - I just copied your code and pasted it in..(right clicked the worksheet tab, view code, paste - save). However in trying again, I now have the filters showing that they are filtering on the content of the relevant cells however no actual results are showing. This would appear to be because column A and cell H6 are dates, they should be in the format 1/11/08 (for the first of November) but the filter is looking for 11/1/08(11th of January).

Also - can it be set to refresh the data on the changing of the content of H4 or H6.

We're definetely getting there...

 

[Jon von der Heyden]

Dates? Ok try:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DoFilter()<br><br><SPAN style="color:#00007F">Dim</SPAN> rCrit1 <SPAN style="color:#00007F">As</SPAN> Range, rCrit2 <SPAN style="color:#00007F">As</SPAN> Range, rRng1 <SPAN style="color:#00007F">As</SPAN> Range, rRng2 <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">With</SPAN> Application<br>    .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> rCrit1 = Range("H4")<br><SPAN style="color:#00007F">Set</SPAN> rCrit2 = Range("H6")<br><br><SPAN style="color:#00007F">Set</SPAN> rRng1 = Range("A18:Q2300")<br><SPAN style="color:#00007F">Set</SPAN> rRng2 = Range("A19:Q2300")<br><br><SPAN style="color:#00007F">With</SPAN> rRng1<br>    .AutoFilter field:=1, Criteria1:=CDate(rCrit1.Value), Operator:=xlOr<br>    .AutoFilter field:=2, Criteria1:=CDate(rCrit2.Value)<br>    rRng2.SpecialCells(xlCellTypeVisible).EntireRow.Copy <SPAN style="color:#007F00">'or do something else</SPAN><br>    <SPAN style="color:#007F00">'.AutoFilter</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ppleasebob]

Jon,

Thanks for your patience here, now I get a type mismatch error

for the sake of good order, cell H4 is the division, which are listed in column B, cell h6 is the date, which are listed in column A. The dates are formatted to be mmm yy.

I noticed that when you set rRng2 = Range ("A19:Q2300") - should this not be ("A18:Q2300") ?

Are you formatting dates on both filters?

Thanks again

Bob