Combine two Private Sub Worksheet_Change(ByVal Target As Range)

[nealtd]

Hi,

I have two events I am trying to combine on one sheet that I am having difficulty with. Both are where you select a number in one cell and it unhides that many rows. When I combine them, only the top one works. I have tried switching them and still the same results.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range, n1 As Long, n2 As Long
Set r1 = Me.Range("C9")
Set r2 = Me.Range("C33")

If Not Intersect(Target, r1) Is Nothing Then
With Me.Range("A10:A29")
.EntireRow.Hidden = True
n1 = r1.Value
If n1 > 0 Then
.Cells(1, 1).Resize(n1, 1).EntireRow.Hidden = False
End If

If Not Intersect(Target, r2) Is Nothing Then
With Me.Range("A34:A53")
.EntireRow.Hidden = True
n2 = r2.Value
If n2 > 0 Then
.Cells(1, 1).Resize(n2, 1).EntireRow.Hidden = False
End If

End With
End If
End With
End If

End Sub

 

[Z51]

It seems your End With's and End If's are a bit messed up... try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range, n1 As Long, n2 As Long
Set r1 = Range("C9")
Set r2 = Range("C33")

If Not Intersect(Target, r1) Is Nothing Then
    With Range("A10:A29")
        .EntireRow.Hidden = True
        n1 = r1.Value
        If n1 > 0 Then
            .Cells(1, 1).Resize(n1, 1).EntireRow.Hidden = False
        End If
    End With
End If

If Not Intersect(Target, r2) Is Nothing Then
    With Range("A34:A53")
        .EntireRow.Hidden = True
        n2 = r2.Value
        If n2 > 0 Then
            .Cells(1, 1).Resize(n2, 1).EntireRow.Hidden = False
        End If
    End With
End If
End Sub

 

[nealtd]

That did it! Thank you so much!