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

Question

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.

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 :)

Answer 1

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