Hide unselected rows... An impossible feat?

[FaithGranger]

I have spent no less than 12 hours looking for a solution to my problem. Hitting a brick wall. What I want to do sounds simple enough, really, so why doesn't Excel seem to offer that feature and more importantly how do I solve the problem?

This is where you awesome experts come in (hopefully)

TASK: To hide rows that are not currently selected. (must be able to unhide them later)

ALTERNATIVELY (but less favorite way to go) To copy selected rows into a new sheet. No, the rows are not adjacent, they are scattered here and there.

READ THIS! - How the rows became selected is why this is a challenge. It will not allow me to right click and COPY/PASTE selected rows into a new sheet for example. Let me explain why: I used the "FIND" tool then entered a keyword (the word "FAN") and excel found every entry in my spreadsheet that has the word FAN anywhere in it. So far so good. I then hit CONTROL-A to select all the find results. This selected all the rows on my contact list that pertain to FANS of my film (as I said, these rows are scattered amongst non-fan rows) . Next I wanted to keep the FAN (selected) rows but temporarily hide all the other contacts to isolate just the fans. And hit the brick wall. :/

NOTE: If you are going to give me a macro, be aware I need basic instructions on where and how to place a macro, because me no is expert and I have never used macros before (bare with me - talk to me like I am a 6 year old. Wait... Make that 4 LOL).

I am hopeful you can help me and my (very) tired eyes thank you much !!

 

[RoryA]

With advanced filters, if you put the criteria for each column on the same row, they work as AND criteria; if you put each on a separate row (so you would require 5 rows for the criteria range, including headers) they work as an OR filter. For more flexibility I would probably suggest using formula criteria.

 

[FaithGranger]

With advanced filters, if you put the criteria for each column on the same row, they work as AND criteria; if you put each on a separate row (so you would require 5 rows for the criteria range, including headers) they work as an OR filter. For more flexibility I would probably suggest using formula criteria.

Bare with me, I am new at all this. I am trying to grasp above. I did not understand this part: "if you put each on a separate row (so you would require 5 rows for the criteria range, including headers)" - When I select "Advance Filters" from the DATA tab, it opens up and gives me only 2 boxes where to enter criteria (see pic), I don't see any way to add more "boxes", and are those boxes what you were referring to when you said "row"

So if I wanted to filter for any contact that has the word FAN in any of column F,G,H,J or K what would I type in each criteria box respectively (any chance you could do it on your PC then do a snapshot of the screen so I can see what it should look like and how it is done?)...

 

[RoryA]

The criteria range is a reference to a range where your criteria are. You basically repeat your column headers and then input your criteria below that. (I have a brief blog post on advanced filters here which may demonstrate the layout).

Here's a very simplistic example:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9
2						Fan
3							Fan
4								Fan
5									Fan
6
7	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9
8						Dummy	Dummy	Dummy	Dummy
9						Dummy	Dummy	Dummy	Dummy
10						Fan	Dummy	Dummy	Dummy
11						Dummy	Dummy	Dummy	Dummy
12						Dummy	Fan	Dummy	Dummy
13						Dummy	Dummy	Dummy	Dummy
14						Dummy	Dummy	Dummy	Dummy
15						Dummy	Dummy	Dummy	Dummy
16						Dummy	Dummy	Fan	Dummy
17						Dummy	Dummy	Dummy	Dummy
18						Fan	Dummy	Dummy	Dummy
19						Dummy	Dummy	Dummy	Dummy
20						Dummy	Dummy	Dummy	Fan
21						Dummy	Dummy	Dummy	Dummy
Sheet1

For this layout you would have A7:I21 as the List range and A1:I5 as the criteria range.

 

[István Hirsch]

I have spent no less than 12 hours looking for a solution to my problem. Hitting a brick wall. What I want to do sounds simple enough, really, so why doesn't Excel seem to offer that feature and more importantly how do I solve the problem?

This is where you awesome experts come in (hopefully)

TASK: To hide rows that are not currently selected. (must be able to unhide them later)

ALTERNATIVELY (but less favorite way to go) To copy selected rows into a new sheet. No, the rows are not adjacent, they are scattered here and there.

READ THIS! - How the rows became selected is why this is a challenge. It will not allow me to right click and COPY/PASTE selected rows into a new sheet for example. Let me explain why: I used the "FIND" tool then entered a keyword (the word "FAN") and excel found every entry in my spreadsheet that has the word FAN anywhere in it. So far so good. I then hit CONTROL-A to select all the find results. This selected all the rows on my contact list that pertain to FANS of my film (as I said, these rows are scattered amongst non-fan rows) . Next I wanted to keep the FAN (selected) rows but temporarily hide all the other contacts to isolate just the fans. And hit the brick wall. :/

NOTE: If you are going to give me a macro, be aware I need basic instructions on where and how to place a macro, because me no is expert :wink: and I have never used macros before (bare with me - talk to me like I am a 6 year old. Wait... Make that 4 LOL).

I am hopeful you can help me and my (very) tired eyes thank you much !!

Found the problem in post #1 interesting, but I have not looked through pages 1-5 very carefully, so I apologize if somebody has already come up with a similar workaround. Hope not.

Choose a helper column (now D) and put in D1:

=IF(COUNTIF(A1:C1,"*fan*"),"","#") and copy down.

Now select column D, go to Home/Find, Enter # in the Find box, click on Options and in the „Look in” box select „Values”, then click „Find All”.
Press Ctrl – A to select rows which do not contain „fan”. Hide them.

Excel Workbook
	A	B	C	D
1	This is a fan here	I do not like them	I hate them
2	Not interested	Brrrr	Worst I know	#
3	I prefer U2	Never seen or heard	The Best Fan Club
4	Not my favourite	Who are they	No comment	#
Sheet

 

[FaithGranger]

Found the problem in post #1 interesting, but I have not looked through pages 1-5 very carefully, so I apologize if somebody has already come up with a similar workaround. Hope not.

Choose a helper column (now D) and put in D1:

=IF(COUNTIF(A1:C1,"*fan*"),"","#") and copy down.

Now select column D, go to Home/Find, Enter # in the Find box, click on Options and in the „Look in” box select „Values”, then click „Find All”.
Press Ctrl – A to select rows which do not contain „fan”. Hide them.

*	A	B	C	D
1	This is a fan here	I do not like them	I hate them	*
2	Not interested	Brrrr	Worst I know	#
3	I prefer U2	Never seen or heard	The Best Fan Club	*
4	Not my favourite	Who are they	No comment	#

<colgroup><col style="width:30px; "><col style="width:146px;"><col style="width:175px;"><col style="width:165px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Interesting work around but I guess what I gather so far is that there is no "easy" way to tell excel to reverse a selection? And if not, is there an easier way to find records that do not contain what you are looking for, using the FIND feature?