Hide unselected rows... An impossible feat?

F

FaithGranger

New Member
Joined
Jul 23, 2014
Messages
38
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 !! :)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Would creating another column to add a reference or tag to identify your fan and non fan then use autofilter work for you?
 
Upvote 0
warhead92100 said:
Would creating another column to add a reference or tag to identify your fan and non fan then use autofilter work for you?
Click to expand...

Regrettably not, because filtering in Excel is column specific, not row specific AND also because filtering is sequential as opposed to simultaneous, meaning you have to filter a first column first, then you can add filter to a second one (but that second one has already been affected by your applying filter to previous column). I have 4 columns for "keywords". A contact might be a combination of several networking possibilities, for example a contact might be a FAN, a CAR OWNER, a CAR CLUB member and a PROMOTER of a car show for example. In which case all 4 columns will have keywords in them. So if I am emailing an announcement to my fans, I will look up FAN and that contact will show up. But if I am looking for classic cars for my next film, then I would search for CAR OWNER and that same dude would also come up. If I want to contact car clubs, same idea.

So after spending many hours yesterday trying to figure out the best way to approach this, seemed the FIND feature worked best... Expect now how do I then isolate those wanted "found" contacts from the rest.

I am annoyed at the fact that Excel will not even let me reverse my selection. If it did (say like photoshop does "Invert selection" of an image) then all I would have to do is FIND the contacts via a keyword, hit SELECT ALL within the find form, then reverse my selection and hit "HIDE" and VOILA :)

But no such luck :(
 
Upvote 0
A simple macro will work if you can create a temporary sheet. The macro can even clear the sheet once it's run again, so you don't need to find or filter. I just need to know the sheet name you want to look at (that has the data in it), and then the four columns that contain the potential information.

I assume, as per your statement, that (let's say) columns P, Q, R, and S can all contain "CAR OWNER", "CAR CLUB", "FAN", or "PROMOTER" in ANY ORDER.

Example:

Row P458 might be "FAN", but R546 can also be "FAN"
 
Upvote 0
NeonRedSharpie said:
A simple macro will work if you can create a temporary sheet. The macro can even clear the sheet once it's run again, so you don't need to find or filter. I just need to know the sheet name you want to look at (that has the data in it), and then the four columns that contain the potential information.

I assume, as per your statement, that (let's say) columns P, Q, R, and S can all contain "CAR OWNER", "CAR CLUB", "FAN", or "PROMOTER" in ANY ORDER.

Example:

Row P458 might be "FAN", but R546 can also be "FAN"
Click to expand...

yes yes :) exactly! Sheet name is CONTACTS , column currently are F,G,H,I,J -

I was not clear on what you meant when you said: "if you can create a temporary sheet. The macro can even clear the sheet once it's run again, so you don't need to find or filter." Pleas rephrase / elaborate a tad... Also, will I be able to do a non-destructive filtering with that macro, aka: reversible? Or will I have to duplicate my sheet into another sheet prior to doing the query because all data, other than the search results, will be lost?

Another question: If I insert extra columns in the sheet in the future, it will shift data to different column letters. I assume the macro will not auto-update so i would have to manually alter it and change the column letters in the code, correct?
 
Upvote 0
Here is another question for everyone: if I was to put all my keywords, separated by /, in same column, I could then use the filtering "search text" function, I tried it and it does work well but... Can one enter more than one keywords at the same time in the text search box? For example say I want to isolate all the FANS and FRIENDS in one step. Currently I can only do either or (one at a time), but if I could do more than one, it might work well for my needs (example: FANS, FRIENDS). I tried a few possible combinations but all failed.

Wanna laugh a bit? Early this morning while I was trying the FIND function and SELECT ALL somehow I must have pressed a key by mistake and it actually filtered my spreadsheet perfectly like I want (only search results were kept). Problem is, I have NO IDEA what I did to make it do that, and try as I may I have not been able to do it again LOLOL. But... I now know it CAN be done, there is a button / short cut somewhere that will do that... If only we could figure it out...
 
Upvote 0
In normal terms, here's what the macro will do:

1) Prompt the user for a search term
2) Search through all records in all five columns for the search term
3) If the search term is found, it will copy the entire row into a new sheet
3a) At this point, if the sheet is not found, it will create the sheet
3b) If the sheet is found, it will clear all data from the sheet

That's it. The new sheet (Will probably call it TempView) is completely static. It will not change until the macro is rerun. Your raw data will not be impacted in any way, shape, or form.
 
Upvote 0
NeonRedSharpie said:
In normal terms, here's what the macro will do:

1) Prompt the user for a search term
2) Search through all records in all five columns for the search term
3) If the search term is found, it will copy the entire row into a new sheet
3a) At this point, if the sheet is not found, it will create the sheet
3b) If the sheet is found, it will clear all data from the sheet

That's it. The new sheet (Will probably call it TempView) is completely static. It will not change until the macro is rerun. Your raw data will not be impacted in any way, shape, or form.
Click to expand...

Oooo that sounds pretty sweet :). So the temp sheet is like a destination for the search result to reside at, until I run another search which will then override my last one (write on top of it, erasing the previous data), correct? And in the event I wanted to keep that data I filtered with the macro, I could just copy temp sheet to another sheet and name it for example "FANS@07232014", and then that duplicate copy will be permanent, correct?

If yes to above this sound likes the way to go :). Will I be able to search for more than one word in the same search (say I want to find anyone who has a vintage car or motorcycle within my contacts) and if so, how do I type the two words (separated with commas, space, asterix or??)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
423
kevin9999
K
K
  • Question
Hiding Rows While Maintaining Page Layout
Replies
2
Views
293
kkobmann
K
M
  • Question
Creating Results Table + Hiding columns based on text in specific row
Replies
4
Views
553
MelissaBr
M
K
  • Solved
VBA How To Hide and Unhide rows
2
Replies
10
Views
1K
kkoruni
K
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A

Forum statistics

Threads
1,214,984
Messages
6,122,601
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top