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