How do I hide specific rows based on values in a different worksheet?

**mgolou** · 03-07-2014, 04:37 PM

Hey guys,

I'm a little new so bare with me. Say I have two worksheets, "Sheet1" and "Sheet2". Let's also say A1 in Sheet1 could have one of four values: cat, dog, rabbit, mouse. Now, based on which value cell A1 on Sheet1 is, I need different rows hidden in Sheet 2. For argument sake let's say if cat is chosen rows 1-5 are hidden, if dog, 6-10 and so on.

Any help is appreciated, thanks!

**Leith Ross** · 03-07-2014, 06:51 PM

Hello mgolou,

Welcome to the Forum!

You can change this macro to what you need...

Sub HideRows()

    Dim Wks1 As Worksheet
    Dim Wks2 As Worksheet
    
        Set Wks1 = Worksheets("Sheet1")
        Set Wks2 = Worksheets("Sheet2")
        
        Select Case Wks1.Range("A1")
            Case Is = "cat":    Wks2.Range("1:5").EntireRow.Hidden = True
            Case Is = "dog":    Wks2.Range("6:10").EntireRow.Hidden = True
            Case Is = "rabbit": Wks2.Range("11:15").EntireRow.Hidden = True
            Case Is = "mouse":  Wks2.Range("16:20").EntireRow.Hidden = True
        End Select
        
End Sub

**mgolou** · 03-10-2014, 11:48 AM

Thanks for the reply, Leith.

However, I pasted the macro into the Sheet2 object, but it didn't seem to do anything.

Any idea why? I should also mention that instead of "cat, dog, etc" I'm using a string of words, and my worksheets are named differently. I of course altered the formula accordingly.

**Leith Ross** · 03-10-2014, 07:28 PM

Hello mgolou,

You should post your workbook. It will make finding the problem easier.

How To Post Your Workbook

At the bottom right of the Reply window, Click the button Go Advanced
At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
Click the Select Files button at the bottom middle of the dialog.
In the new window Find the file you want to upload, select it, and Click Open.
You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
Wait until the file has completely uploaded before you exit the File Manager dialog.

**mgolou** · 03-11-2014, 10:16 AM

Hey Leith,

Unfortunately the workbook has a little bit of private information in it which I cannot share. However, the reason your formula wasn't working was due to the Sub name, once I changed the sub name to Worksheet_Change (ByVal Target As Range) it worked! However, is there any way to unhide the rows if I was to toggle my selection? Say I chose cat, but later changed it to dog, how would I get the original cells to unhide?

Cheers

**mgolou** · 03-12-2014, 01:04 PM

Hey Leith,

I've solved the problem. I just created buttons on Sheet 1 and programmed

Worksheets("Sheet2").Range("1:10").EntireRow.Hidden = False
  Worksheets("Sheet2").Range("10:100").EntireRow.Hidden = True

And simply hide the entire worksheet.

Thanks for your help!

How do I hide specific rows based on values in a different worksheet?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How do I hide specific rows based on values in a different worksheet?

Re: How do I hide specific rows based on values in a different worksheet?

Re: How do I hide specific rows based on values in a different worksheet?

Re: How do I hide specific rows based on values in a different worksheet?

Re: How do I hide specific rows based on values in a different worksheet?

Re: How do I hide specific rows based on values in a different worksheet?

Thread Information

Users Browsing this Thread

Similar Threads

To hide/unhide rows based on specific value in a cell.

[SOLVED] First unhide all rows - then hide rows based on specific cell value for a range of cells

Excel 2007 : Help with Conditional formatting or VBA to hide rows based on specific criteria

Hide/Unhide Rows in One Worksheet Based on Values in Cells in Another Worksheet

Show/Hide Specific set of Rows based on Criteria in one cell

Bookmarks

Bookmarks

Posting Permissions