Use of code more than once on the same worksheet

[agc3278]

Hi All,

I cannot seem to figure out how to get this code to work multiple times on the same Worksheet (I am definitely a beginner when it comes to VBA). So much so, that an awesome member helped out on this forum with the below code to hide and show rows (2-43) based on a number entered into a specific cell (A1). What I'm looking for is a way to hide and show two more ranges of rows based on a entries into two other cells (A2 and A3). Any help or guidance would be greatly appreciated.

Thank you,
Gary

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aNames, Val
Dim i As Long, ShowHideRows As Long, ShowRows As Long

Const sNames As String = "Sheet1, Sheet2"
Const RowCell As String = "A1"
Const FirstVisRow As Long = 2
Const MaxHideRow As Long = 43

If Target.Address(0, 0) = RowCell Then
aNames = Split(sNames, ", ")
ShowHideRows = MaxHideRow - FirstVisRow + 1
Val = Target.Value
ShowRows = IIf(IsEmpty(Val), ShowHideRows, Val)
Application.ScreenUpdating = False
For i = 0 To UBound(aNames)
With Sheets(aNames(i)).Rows(FirstVisRow)
.Resize(ShowHideRows).Hidden = True
If ShowRows > 0 Then .Resize(ShowRows).Hidden = False
End With
Next i
Application.ScreenUpdating = True
End If
End Sub

 

[Worf]

Hi

Dim anames
Private Sub Worksheet_Change(ByVal Target As Range)
Dim first, maxhide, j%, ad
Const sNames$ = "Sheet1, Sheet2"
first = Array(2, 15, 30)
maxhide = Array(10, 20, 40)
ad = Array("a1", "a2", "a3")
If Not Intersect(Target, [a1:a3]) Is Nothing Then
    anames = Split(sNames, ", ")
    Application.ScreenUpdating = False
    For j = LBound(first) To UBound(first)
        Aux first(j), maxhide(j), Range(ad(j))
    Next
    Application.ScreenUpdating = True
End If
End Sub


Sub Aux(ByVal first%, ByVal maxhide%, tgt As Range)
Dim i%, showhide%, showrows%
showhide = maxhide - first + 1
showrows = IIf(IsEmpty(tgt), showhide, tgt)
For i = 0 To UBound(anames)
    With Sheets(anames(i)).Rows(first)
        .Resize(showhide).Hidden = True
        If showrows > 0 Then .Resize(showrows).Hidden = 0
    End With
Next
End Sub