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

 

[Rick Rothstein]

Splendid thank you !! I selected a few rows, then ran (or tried to) your macro but it gave me an error message: "Can't execute code in break mode" .... Awaiting your input...

I am not sure what you did to enter break mode, but that means that you had code running and either you manually paused it or it paused itself (there is code that can make a running program do that). To make sure you clear that break mode status, press ALT+F11 to go into the VB editor (assuming you are not already there) and click Run/Reset on the VB editor's menu bar, then go back to the worksheet and press ALT+F8 and then run my macro.

 

[FaithGranger]

Column A being blank does pose a problem for my macro, which is why it was returning no values. There is a line of code:

endRow = Sheets(dataSheet).Cells(Rows.Count, "A").End(xlUp).Row

What this code does it assign a maximum value for the macro. It looks at all the used cells in column A, of which there are none. So, changing that "A" to "F" will change your results and should force it to work correctly.

endRow = Sheets(dataSheet).Cells(Rows.Count, "F").End(xlUp).Row

yes in deed , it fixed the problem. THANK YOU!! I did get search result in the TempView sheet !!!! Very cool. a FEW MORE PROBLEMS TO RESOLVE, if this idea is to do the job like the FIND function did:

1. One big problem. None of the cell comments are copied over. I rely heavily on comments to store all details about each contact, especially what our conversation was about, so it is vital I still have that info there. I am sorry I did to think about mentioning that from the get go, I took it for granted that when you copy paste, comments are pasted along with the cells they belong to. Both FILTER or SORT and even FIND excel function do keep comments and formatting intact... Doing it with the macro search however, is not the case :/

2. Less essential but still needed: Can the formatting also be copied over (CELL COLOR AND FONT COLOR AND TYPESET)? I was able to fix the column width and it remains the same every time new data populated the sheet, but the formatting is erased every time and goes back to white cells and black typeset, no color coding.

3. Can the search be non case sensitive? I am not always very consistent (although I try) in the way I type words (which is why I just switched to constrained drop down list for those keyword columns...)

4. It dunned on me that I may want to search for something else than keywords, such as a state, or city etc. Can the search columns be extended (say from E to T) to encompass most useful column, such as country, contact name etc... This way If I want to email all the contacts from AUSTRALIA for example, I can search AUSTRALIA (currently I cannot, cause country column is not included in our range). The useful range is E (name column) thru T (city)

many thanks for your kind help !!

 

[FaithGranger]

I am not sure what you did to enter break mode, but that means that you had code running and either you manually paused it or it paused itself (there is code that can make a running program do that). To make sure you clear that break mode status, press ALT+F11 to go into the VB editor (assuming you are not already there) and click Run/Reset on the VB editor's menu bar, then go back to the worksheet and press ALT+F8 and then run my macro.

thank you Rick, that did do the trick and I was now able to test the macro. But it gave me another error message when it ran:

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

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?

 

[NeonRedSharpie]

Sub hideUnselectedRows()

    
Dim startRow As Integer
Dim endRow As Long


Dim nextRow As Long


Dim dataSheet As String
Dim outputSheet As String


Dim colRangeStart As Integer
Dim colRangeEnd As Integer


Dim termNumber As Integer
Dim searchTerm As String




    dataSheet = "Contacts"
    outputSheet = "TempView"
    
    
    colRangeStart = 6   'F
    colRangeEnd = 10    'J
    
    nextRow = 2


    startRow = 2
    endRow = Sheets(dataSheet).Cells(Rows.Count, "A").End(xlUp).Row
        
            searchTerm = InputBox(Prompt:="Your search term.", _
                Title:="Enter your search term", Default:="Your Search Term Here")
                
            If searchTerm = "Your search term here" Or _
                searchTerm = vbNullString Then
               
                Exit Sub
            Else


        Sheets(outputSheet).Cells.Clear
        Sheets(outputSheet).Cells(1, 1).EntireRow.Value = Sheets(dataSheet).Cells(1, 1).EntireRow.Value
        
        For x = startRow To endRow Step 1
            For y = colRangeStart To colRangeEnd Step 1
                If Sheets(dataSheet).Cells(x, y).Value = searchTerm Then
                    Sheets(dataSheet).Cells(x, y).EntireRow.Copy
                    Sheets(outputSheet).Cells(nextRow, 1).EntireRow.PasteSpecial
                    nextRow = nextRow + 1
                    Exit For
                End If
            Next y
        Next x
        
        End If

Try that to fix your formatting/copy/paste function. If that works, I'll start looking at the others. No promises though because once you start searching for different things, we're getting into a userform situation. You can always just modify the columns being looked at with "colRangeStart" and "colRangeEnd".

 

[FaithGranger]

Mmmmm... For some reason the above macro is not finding anything at all. It does run (Initially it gave me error message, I think you forgot a few lines at the end, but I copied them over from your previous macro, then it ran OK) but justy copies the header row and nothing else...

Sub hideUnselectedRows()

    
Dim startRow As Integer
Dim endRow As Long


Dim nextRow As Long


Dim dataSheet As String
Dim outputSheet As String


Dim colRangeStart As Integer
Dim colRangeEnd As Integer


Dim termNumber As Integer
Dim searchTerm As String




    dataSheet = "Contacts"
    outputSheet = "TempView"
    
    
    colRangeStart = 6   'F
    colRangeEnd = 10    'J
    
    nextRow = 2


    startRow = 2
    endRow = Sheets(dataSheet).Cells(Rows.Count, "A").End(xlUp).Row
        
            searchTerm = InputBox(Prompt:="Your search term.", _
                Title:="Enter your search term", Default:="Your Search Term Here")
                
            If searchTerm = "Your search term here" Or _
                searchTerm = vbNullString Then
               
                Exit Sub
            Else


        Sheets(outputSheet).Cells.Clear
        Sheets(outputSheet).Cells(1, 1).EntireRow.Value = Sheets(dataSheet).Cells(1, 1).EntireRow.Value
        
        For x = startRow To endRow Step 1
            For y = colRangeStart To colRangeEnd Step 1
                If Sheets(dataSheet).Cells(x, y).Value = searchTerm Then
                    Sheets(dataSheet).Cells(x, y).EntireRow.Copy
                    Sheets(outputSheet).Cells(nextRow, 1).EntireRow.PasteSpecial
                    nextRow = nextRow + 1
                    Exit For
                End If
            Next y
        Next x
        
        End If

Try that to fix your formatting/copy/paste function. If that works, I'll start looking at the others. No promises though because once you start searching for different things, we're getting into a userform situation. You can always just modify the columns being looked at with "colRangeStart" and "colRangeEnd".

 

[NeonRedSharpie]

I forgot to change:

[COLOR=#333333][I]    endRow = Sheets(dataSheet).Cells(Rows.Count, "A").End(xlUp).Row[/I][/COLOR]

to

[COLOR=#333333][I]    endRow = Sheets(dataSheet).Cells(Rows.Count, "F").End(xlUp).Row[/I][/COLOR]

 

[FaithGranger]

I forgot to change:

[COLOR=#333333][I]    endRow = Sheets(dataSheet).Cells(Rows.Count, "A").End(xlUp).Row[/I][/COLOR]

to

[COLOR=#333333][I]    endRow = Sheets(dataSheet).Cells(Rows.Count, "F").End(xlUp).Row[/I][/COLOR]

You are BRILLIANT - It worked, with formatting and comments!!!!!!!!!!!!!!!!!!
It scared me a bit at first because the sheet was dancing like crazy and took about 15 seconds or so to transfer over (formatting and all) and at first I thought the software had gone mad... But now I know it is "normal" (in case someone else tries this, expect the weird dance and don't panic LOL)

Are you still up for trying to make it non case sensitive? Oh and what is our search range currently, can we expand it to cover names and location (city etc) or will it cause malfunction? I had tried to change the range but when I did, it would not find anything anymore, did you test it?

Question: Can an * be used to replace letters with this search system? Ex search for JOHN* to find any johns in my contact database, regardless of last name (I tried it but it did not seem to work for me - ex: VOLUNT* instead of typing VOLUNTEER)

 

[NeonRedSharpie]

An * should be able to be used, yes. As for case sensitive or not, I've never worked with it so I can't really speak to it. I rarely use inputboxes and this is probably my first working macro using one.

As for the range, you should be able to change it without consequence. It just needs to be number and the colRangeStart needs to be less than colRangeEnd. There shouldn't be any other qualifications.

 

[FaithGranger]

An * should be able to be used, yes. As for case sensitive or not, I've never worked with it so I can't really speak to it. I rarely use inputboxes and this is probably my first working macro using one.

As for the range, you should be able to change it without consequence. It just needs to be number and the colRangeStart needs to be less than colRangeEnd. There shouldn't be any other qualifications.

OK will try to change range in macro, will let you know outcome. Meanwhile the " * " failed for me, did you test it on your end?

(....) I AM BACK - i CHANGED THE RANGE OF CELLS AND IT WORKED So I can now search a larger range... I tested again the " * " and still not working. I typed in DISTRIB* instead of DISTRIBUTION and it didn't find those entries. It copied a blank sheet with only headers.

 

[NeonRedSharpie]

It's possible that the "*" won't work. Again, I have no experience with input boxes so I really don't know how they parse the text.