Show row if contains text, hide all others

[AusTempest]

Hi everyone,

I've scoured the interwebs for hours now and cannot for the life of me get my code to work.

I have a sheet with a dropdown box in cell "J1" which is meant as a 'Show only rows containing this value'.

Column Q, from row 3 downward has a pick box which lists the same values as the "J1" drop down box, but uses code to combine whats chosen with commas. An example of what is in a cell in column Q is "SeaHawk, BlackHawk, Squirrel, MRH", where cell J1 would be either "SeaHawk" or "MRH" or something completely different.

I need to only show rows, with text in column Q containing the smae text as J1.

Any Ideas?

Thanks Again
AusTempest

[AusTempest]

This is an example of the worksheet i'm working with.

I've deleted ~300+ rows and edited out the text that is not required for this code.

[jmac1947]

Hi Austempest,

I cannot get the code behind the page to fire, my first trick (after not understanding the code itself ) is to slap in a break point on the first executable line of the code so I can at least try the old F8 step through trick... cannot get it to break so I am guessing it is probably something to do with the worksheet change process (which is sadly a touch past where I can be really helpful )

and by the way, welcome to to forums, someone will be able to help even if it isn't me just now

Jmac

[analystbank]

Try to filter on blank rows, and then hide. since Filter is an inbuilt function within excel, it would work faster regardless of row numbers.

[Richard Buttrey]

Hi,

I don't understand your difficulty since your column R formula seems to be giving you what you want (i.e. where the text in J1 exists somewhere in the Q cell) and hence just filter column R.

If you're looking for an exact match then simply

Please Login or Register  to view this content.

[AusTempest]

Thanks for the replies

I got colum R to give me what i wanted, by it wasn't autoimatically hiding or unhiding the rows.

After much playing around last night i did get it to work.

I had to put the following code in the Sheet1 code at the end of the current Worksheet_Change code

Please Login or Register  to view this content.

And then created a HideRows Macro in Module1 with the following code:

Please Login or Register  to view this content.

Proabably not the neatest code in the world, but it works.

Thanks again for your help guys.

[Richard Buttrey]

Hi,

This sort of request really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating a loop step back a bit and consider if there isn't a more efficient way. Loops should only be used as a last resort since they can be very slow particularly when many rows are involved.

The fastest and most efficient way I know of doing this is to use an autofilter and filter for whatever is the relevant criteria. Then select all the filtered rows using the .SpecialCells(xlCellTypeVisible) construct, delete them all, finally remove the autofilter.

[AusTempest]

I would have used an autofilter, but i'm limited to excel 2003 at work and couldn't work out how to do that for a column of text that "included" a particular phrase, as opposes to matching whole cells (hence why i was posting here in the first place )

I could easily get it done at home using excel 2010, but 2003 i couldn't get if to filter to all cells containing "xyz" anywhere in the cell.

[Richard Buttrey]

Hi,

Don't forget that you can always use the wild card character * to find a particular string of characters in another string. e.g.

Please Login or Register  to view this content.

where A1 contains say "Hawk" and B1 contains say "Seahawk". You can wrap the find in an IF(ISERROR()) function to set the result of the find to say either "Hide", or "Show" for that row and then just filter for the word 'Show".