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

 

[NeonRedSharpie]

How many would the maximum number of words you want to search for be? I can set it up however you would like.

 

[Rick Rothstein]

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

 

[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(outputSheet).Cells(nextRow, 1).EntireRow.Value = Sheets(dataSheet).Cells(x, y).EntireRow.Value
                    nextRow = nextRow + 1
                    Exit For
                End If
            Next y
        Next x
        
        End If


End Sub

I wanted to get something out there in case I don't hear back. It look at only one value, attempts to get multiple values were unsuccessful. I don't work a lot with inputbox and msgbox.

How to enter the macro:

You will need to create a tab titled "TempView" because I failed at logic for that too
Right click on the "Contacts" tab.
View Code
Copy and paste the above code into the opened area
Then, if you press the green arrow up above, or press "F5", it will run the macro.

 

[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

Ok as I said I have never worked with macros before. I just read 3 tutorials and watched 2 Youtube videos to learned where to go to paste the code. So I pasted it , but now i am back in the worksheet and can't figure out how to run it. One tutorial said to go to FORMULA / INSERT FUNCTION / user defined (from drop down menu) but I do not see that option available in my drop down menu so obviously I did something wrong. Yes I pasted in the proper sheet and I am back in the same sheet, so macro should be there in the INSERT FUNCTION / user defined drop down menu (??)... Need a little help please so I can try that macro you have me (THANK YOU by the way!!)

 

[Rick Rothstein]

Ok as I said I have never worked with macros before. I just read 3 tutorials and watched 2 Youtube videos to learned where to go to paste the code. So I pasted it , but now i am back in the worksheet and can't figure out how to run it. One tutorial said to go to FORMULA / INSERT FUNCTION / user defined (from drop down menu) but I do not see that option available in my drop down menu so obviously I did something wrong. Yes I pasted in the proper sheet and I am back in the same sheet, so macro should be there in the INSERT FUNCTION / user defined drop down menu (??)... Need a little help please so I can try that macro you have me (THANK YOU by the way!!)

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (HideUnselectedRows) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[FaithGranger]

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(outputSheet).Cells(nextRow, 1).EntireRow.Value = Sheets(dataSheet).Cells(x, y).EntireRow.Value
                    nextRow = nextRow + 1
                    Exit For
                End If
            Next y
        Next x
        
        End If


End Sub

I wanted to get something out there in case I don't hear back. It look at only one value, attempts to get multiple values were unsuccessful. I don't work a lot with inputbox and msgbox.

How to enter the macro:

You will need to create a tab titled "TempView" because I failed at logic for that too
Right click on the "Contacts" tab.
View Code
Copy and paste the above code into the opened area
Then, if you press the green arrow up above, or press "F5", it will run the macro.

Reporting back from the battle field : OK I was able to figure out how to paste it, access it and run it. It did run, did not give me any error messages, however all it did was paste the header into the temp sheet LOL, as in, it didn't find squat ... I tried 3 times, same outcome.... Mmmmm...

 

[NeonRedSharpie]

Verify that your data begins at row 2 and doesn't include any significant blanks. Is column A populated? Also verify that the correct columns to search are F:J.

 

[FaithGranger]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (HideUnselectedRows) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

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

 

[FaithGranger]

Verify that your data begins at row 2 and doesn't include any significant blanks. Is column A populated? Also verify that the correct columns to search are F:J.

1. Data begins at row 2, row 1 being headers.
2. Column 1 thru 4 are currently empty, I intend to store pics of the cars and props my contacts email me in those columns (perhaps the topic of my next thread LOL) does that mess up your macr, to have those column empty (or filled with pics?)
3. Otherwise no blanks in terms of rows, and no other empty columns in worksheet
4.Yes search column are in deed F-J and you had the column numbers correct, I did double check that earlier

 

[NeonRedSharpie]

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