Show/hide rows based on drop down cell using named ranges

**Stu091074** · 02-05-2014, 06:10 PM

hi

I've found various threads showing how to hide/unhide rows based on a value in a cell and built a form based on the code I have found. The form has three uses and shows hides sections (rows) of the form dependant on the option selected in a cell (B1) This works fine but I now have a requirement for users of this form to add addtional rows into some of the sections but as they do this sections further down the form move down so subsequent sections then have different row numbers to that in the VB. I thought that I could use named ranges instead of row numbers but as soon as I put in the named range I get runtime 13 mismatch error?

Please help.

The attached sample has a drop down in cell B1 for options A, B or C and dependant on this selection, groups of rows are hidden.
Each grouping of rows already has a named ranges (Range1 to Range5) but how do I use these in the VB?

Many thanks

Stuart

**AlphaFrog** · 02-05-2014, 07:16 PM

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "B1" Then
        Application.ScreenUpdating = False
        Select Case Target.Value
            Case "A"
                Range("Range1,Range5").EntireRow.Hidden = True
                Range("Range2,Range3,Range4").EntireRow.Hidden = False
            Case "B"
                Range("Range1,Range4").EntireRow.Hidden = True
                Range("Range2,Range3,Range5").EntireRow.Hidden = False
            Case "C"
                Range("Range3,Range4,Range5").EntireRow.Hidden = True
                Range("Range1,Range2").EntireRow.Hidden = False
        End Select
        Application.ScreenUpdating = True
    End If
End Sub

**Stu091074** · 02-07-2014, 11:00 AM

Doh! So simply when you see the answer. Your solution works perfectly. Thank you for your help.

Stuart

Show/hide rows based on drop down cell using named ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Show/hide rows based on drop down cell using named ranges

Re: Show/hide rows based on drop down cell using named ranges

Re: Show/hide rows based on drop down cell using named ranges

Thread Information

Users Browsing this Thread

Similar Threads

Named Ranges don't show up in drop-down list

Hide rows based on multiple ranges not containing certain value of referenced cell

Need help to show/hide rows based on values from a cell

[SOLVED] Hide/Show rows based on the value in a cell

Toggle Hide/ Show Named Ranges

Bookmarks

Bookmarks

Posting Permissions