Macro to hide rows

[kellerh3]

I'm trying to figure out a way to hide a dynamic range of rows when a particular cell becomes active either via a mouse click or cell navigation using the arrow keys. For instance, in cell A1, i have the word "Hide" while the range "Data" is a series of rows that increases or decreases base on how much data there is. What i'd like to have happen is when either the mouse clicks on A1 or the cursor is moved to a1, the "Data" range is hidden and the word "Show" appears in A1. When A1 is clicked again, the "Data" range appears again.

Can anyone help with this as i am just learning macro code...

 

[Andrew Poulsom]

Put this code in the module for the Worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Address <> "$A$1" Then Exit Sub
        If .Value = "Hide" Then
            .Value = "Show"
        Else
            .Value = "Hide"
        End If
    End With
    With Range("Data").EntireRow
        .Hidden = Not .Hidden
    End With
End Sub

 

[kellerh3]

I must be doing something wrong as i tried putting it into both a module and class module and nothing happens. Doesnt matter if i click on A1 or navigate to it, "Data" does not hide.

 

[Andrew Poulsom]

While the rows in Data are visible, enter Hide in A1, then select another cell. Right click the sheet tab and choose View Code. Paste the code into the window on the right. Press Alt+F11 to return to your worksheet. Try it out by selecting A1.

If it doesn't work make sure macros are enabled.

 

[kellerh3]

Thanks, that works. I appreciate your time...

 

[kellerh3]

If i would like to hide different rows within the same sheet using the method above, do i change the "Worksheet_SelectionChange(ByVal Target As Range)" or do i modify something else?

 

[Andrew Poulsom]

You would need to add more code to the existing event procedure.

 

[kellerh3]

I've tried copying the existing like below but this doesnt work either. Can you provide any direction on what to change?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Address <> "$A$1" Then Exit Sub
If .Value = "Hide" Then
.Value = "Show"
Else
.Value = "Hide"
End If
End With
With Range("Data").EntireRow
.Hidden = Not .Hidden
End With
Range("B1").Select
With Target
If .Address <> "$D$10" Then Exit Sub
If .Value = "Hide" Then
.Value = "Show"
Else
.Value = "Hide"
End If
End With
With Range("Objectives").EntireRow
.Hidden = Not .Hidden
End With
Range("C10").Select
End Sub

 

[Andrew Poulsom]

The code exits if Target is not A1, so it never gets to test for D10. I changed it to test for equality rather tha inequality:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Address = "$A$1" Then
            If .Value = "Hide" Then
                .Value = "Show"
            Else
                .Value = "Hide"
            End If
            With Range("Data").EntireRow
                .Hidden = Not .Hidden
            End With
            Range("B1").Select
        ElseIf .Address = "$D$10" Then
            If .Value = "Hide" Then
                .Value = "Show"
            Else
                .Value = "Hide"
            End If
            With Range("Objectives").EntireRow
                .Hidden = Not .Hidden
            End With
            Range("C10").Select
        End If
    End With
End Sub