Create/Show Rows based on the value in the other cell VBA

I was hoping for some help.

I have a dropdown list in cell "I2" and Hidden Name Ranges. I would like to show Name Range 1 if 1 is selected in "I2" cell. Show Name Range 1 and Name Range 2 if 2 is selected in "I2". Show Name Range 1 and Name Range 2 and Name Range 3 if 3 is selected in "I2" and so on.

enter image description here

I know I could use If statement for all 30 possible combinations but this would be very repetitive

If Worksheets("sheet1").Range("I2").Value = 1 Then
            Range("NameRange1).EntireRow.Hidden = False
            Range("NameRange2).EntireRow.Hidden = True
            Range("NameRange3).EntireRow.Hidden = True
            .... and so on 

Is there another efficient way to for example a loop through the range to hide unhide desired rows ? Or perhaps there is a separate procedure I could use to create rows (row 6, row 7 row 8 etc ..)'On the fly' based on the value in cell "I2" . That way I could instantly create rows that are needed. I'm sorry if I'm being vague but considering best possible options here

Any advise hugely appreciated. Thanks :)


  • The following routine will show ranges up to a certain number and hide all the others. It will loop over all ranges and check if the range number is larger than the given number showUntilRange. As the hidden-property is a boolean, you can simply use the term (i > showUntilRange).

    Sub showHideRanges(showUntilRange As Long)
        Const rangeCount As Long = 30
        Const rangeNamePrefix As String = "NameRange"
        Dim i As Long, rangeName As String, r As Range
        For i = 1 To rangeCount
            rangeName = rangeNamePrefix & i
            On Error Resume Next
            Set r = Nothing
            Set r = Range(rangeName)
            On Error GoTo 0
            If r Is Nothing Then
                Debug.Print "couldn't find range " & rangeName
                r.EntireRow.Hidden = (i > showUntilRange)
            End If
        Next i
    End Sub

    Your event routine could look like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("I2")) Is Nothing Then Exit Sub
        showHideRanges CLng(Target)
    End Sub