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 !!

 

[FaithGranger]

NeonRedSharpie's suggestion sounds like a reasonable approach, but to answer the question you asked originally, give this code a try...

Sub HideUnselectedRows()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Selection.EntireRow.Hidden = True
  Intersect(ActiveSheet.Cells.SpecialCells(xlVisible).EntireRow, Columns("F:J")).Select
  Rows.Hidden = False
  Selection.EntireRow.Hidden = True
End Sub

Good morning Rick ... Don't give up on me just yet. Even though a macro was successfully created in sorting and making a brand new spreadsheet from results, I am still very interested in the simpler solution, which is to reverse selection so I can then delete or hide the unwanted search results. The reason being that the macro we currently have does not allow me to search for more than one word at a time, while the FIND function of excel will. So I can see how having both macros would be very useful.

So I reported yesterday that your macro is giving me an error message, did you see the post and what are your thoughts? Is it fixable?

error was: "Run-time error '1004':
Unable to set the hidden property of the range class"

I hit "debug" and it highlighted this line within your macro: " Selection.EntireRow.Hidden = True"

NOTE : for this first test, I simply manually selected 6 rows using my mouse (as opposed to the FIND function) then ran the macro. I assume that is OK and should have no bearing on the results?

 

[Rick Rothstein]

Good morning Rick ... Don't give up on me just yet. Even though a macro was successfully created in sorting and making a brand new spreadsheet from results, I am still very interested in the simpler solution, which is to reverse selection so I can then delete or hide the unwanted search results. The reason being that the macro we currently have does not allow me to search for more than one word at a time, while the FIND function of excel will. So I can see how having both macros would be very useful.

So I reported yesterday that your macro is giving me an error message, did you see the post and what are your thoughts? Is it fixable?

error was: "Run-time error '1004':
Unable to set the hidden property of the range class"

I hit "debug" and it highlighted this line within your macro: " Selection.EntireRow.Hidden = True"

NOTE : for this first test, I simply manually selected 6 rows using my mouse (as opposed to the FIND function) then ran the macro. I assume that is OK and should have no bearing on the results?

I tested the code before I posted it and again just now... I cannot make it not work no matter what I try. Can you describe step-by-step (so I can duplicate it here) what you are doing to make that error appear? Also describe what cells are filled on your sheet and which cells you are selecting. Also, what version of Excel are you using?

 

[FaithGranger]

I tested the code before I posted it and again just now... I cannot make it not work no matter what I try. Can you describe step-by-step (so I can duplicate it here) what you are doing to make that error appear? Also describe what cells are filled on your sheet and which cells you are selecting. Also, what version of Excel are you using?

I tried it again just now, same error message. Answers to above questions:

1. Excel 2010(Office 2010)
2. First 4 columns are currently empty and can be hidden as a group.
3. I have 3 groups of cells that I can hide, althought when I ran the test I unhid them all, just in case.
4. Data is text (names etc), numbers (phone nbs) and emails with hyperlinks. Some cells have comments attached to them. There are 4 column dedicated to "keywords" that have drop down list to restrict input to only the words in the list.
5. Light formatting on spreadsheet nothing fancy, just cell color and text size, type and color.

Basically above is my contact list

WHAT I AM DOING:

I simply selected a few rows (so there is an active selection to be reversed) then I press ALT + F8 to access macros, selected your macro from my macro list, clicked on it then RUN.

 

[FaithGranger]

Rick Rothstein did you see my above post? Do you need any other info I didn't provide? Let me know, many thanks!

 

[RoryA]

It sounds to me as though an Advanced Filter would do what you want. (It also sounds to me as though you should be using a database program )

 

[Rick Rothstein]

Rick Rothstein did you see my above post? Do you need any other info I didn't provide? Let me know, many thanks!

I tried to set up a worksheet to match what you described and still the macro I posted works here for me, so I am not entirely sure why you are having trouble with it. If you send a copy of the workbook that my code does not work with to me, I will look at it and try to figure out how to make my code work with it. Here is my email address and please include the title of this thread in your email message so I can find my way back here more easily...

rick DOT news AT verizon DOT net

 

[FaithGranger]

It sounds to me as though an Advanced Filter would do what you want. (It also sounds to me as though you should be using a database program )

regrettably Advance Filter will not work for me, Because if you refer back to my initial posts, I have 4 columns of keywords that have constrained entry (drop down list), so to keep keywords consistent. Advance Filter filters one column at a time and would remove many contacts that may have that same keyword in one of the 3 other columns.

However, a CTR+F search will look at all columns, and find all results. the problem is then to organize the results. Just re-read the thread to understand the situation, it would take too much time for me to retype it all again...

There is also a good reason why I use Excel for contacts. Because I have other spreadsheets to organize my tours, distribution etc and that they are built to be compatible, info wise, so I can paste an entire contact row into my tour spreadsheet and have all the info regarding my tour stop ready in seconds. In otherwords, the contact info can be pasted into my other spreadsheets when I need it (column order matches etc etc).

If you have suggestion for contact databases programs that can also organize tours, allow me easy and full customization, calculate gas mileage, expenses etc for me, I would certainly love to check into it.

 

[RoryA]

Advanced filters are considerably more powerful than autofilters and can filter using OR criteria on multiple columns.

 

[FaithGranger]

Advanced filters are considerably more powerful than autofilters and can filter using OR criteria on multiple columns.[/QUOTE

I have tried advanced filters and could not get them to look, find and keep any row that has for example the word "FAN" written on any of 4 columns. If you are able to get them to do this, please share step by step what you did, because that would be the best way for me to go, if it worked.

When I tried advanced filters they would filter a first column wth criteria then apply second criteria to the results of the first filtering. And that's is why it didn't find all the records. Some of the contacts might have the word FAN in KEYWORD 1 column, others might have it in KEYWORD 4 column for example. And I need to pull all the contacts that are FANS regardless of WHERE the keyword FAN was entered. the SEARCH function will do that, but then it does not create a filtered list with the results (aka: removing the NON FANS records from my list)

 

[Rick Rothstein]

@FaithGranger,

Just wondering if you saw Message #36 or not?