Is there a way to repeat an if statement in VBA for excel depending on what cell you are on-CodePudding

I'm currently working on building a excel workbook that I want to be able to hide cells based on a data validation. The code I have already works fine.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("C2"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "A": Rows("18:41").EntireRow.Hidden = True
                                          Rows("6:17").EntireRow.Hidden = False
    Case Is = "B": Rows("6:29").EntireRow.Hidden = False
                                          Rows("30:41").EntireRow.Hidden = True
    Case Is = "C": Rows("6:41").EntireRow.Hidden = False
    End Select
End If
End Sub

The problem I have is that this could be repeated up to possibility of 8 times (being done with a similar data validation). But each time the target address would change and the rows that need to be hidden would change. IE Round 2 target address cell would be "C42" and the rows that would need to be hidden would be changing their hidden or not would be rows 46:81.

My initial plan is just to create 8 individual If statements each dedicated to the group in question. I was seeing if there was a cleaner way to repeat this same process.

CodePudding user response：

You can achieve that by storing the values A, B, C and so on, in an array. You should also store the corresponding range addresses in an array. Then it's just a matter of looping to figure out which letter got matched.

The code would look like:

for i = 1 to letters.length
    if target.value = letters(i) then
        Rows(addresses(i)).EntireRow.Hidden = True
        Exit for
    End if
Next