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

 

[Jon von der Heyden]

Hello again Bob

Rng1 is the entire range (including column labels) - therefore assumes that you have column labels / titles in row 18.

Rng2 is the entire data range, that is this range should exclude column labels / titles.

I'm not sure what you intend on doing with the filtered data but I have assumed that if you want to copy and paste it; or delete it, then you would want to do this to the data area only and not include the column labels.

Yes, I assumed both criteria were dates. Change:
Criteria1:=CDate(rCrit1.Value)
to:
Criteria1:=rCrit1.Value

If you continue to get a mis-match then please let me know which line it debugs to (i.e. the line highlighted in yellow).

 

[Yard]

Jon - I just copied your code and pasted it in..(right clicked the worksheet tab, view code, paste - save)

Not sure if Jon's code needs to be in a standard module to work properly? (rather than the Worksheet module)

 

[ppleasebob]

Jon,

Thanks for the changes, I decided to revisit Yard's formulaic solution, given the fact that the spreadsheet is going on a network to be used by quite a few people, if it breaks for any reason, and I'm still teaching myself VB, I best stick to a less complicated solution, thank you very much for your interest, I will look to use your workings for this in the near future.

Yard, thanks also for your interest, I didn't quite get your original solution, having slept on it, a moment of clarity boomed. I have also put a button on my sheet to run the advanced filter to keep it away from prying fingers!, I will use this as a base to expand my sheet to give the options of date ranges and display all of the info or selections of it.

Gents - once again thanks very much.

 

[Jon von der Heyden]

Hi Bob

For future reference you may find this little tutorial on advanced filter handy: http://www.contextures.com/xladvfilter01.html

 

[carling73]

Hi Jon, can you explain how to amend you code to run with just one cell (B2) not two

 

[mrpaw]

This is a little late but wanted to let YARD know his solution was spot on. If I'm always using 2 criteria. I'm using 5 criteria, the user only wants to pick 1 or 2 at the time but the option should be available. Anyway, if they don't choose all five, code bombs. Any idea on how to avoid empty criteria?