Multiply VBA Macro for hiding and unhiding rows

[Bram1212]

Hi!

I found this VBA code that helps me hide/show a specific range when selecting one or the other cell. However I would like to use this multiple times on different cells. For instance when selecting B35 hide row 36:38 and when selecting D35 showing row 36:38. When selecting B39 hide row 40:42 and when selecting D39 showing row 40:42 and so on.

If it would also be possible to have it only trigger on CNTRL+select cell would be awesome.

Current code:

Option Explicit
Private Sub worksheet_selectionchange(ByVal target As Range)
If Selection.Count = 1 Then
If Not Intersect(target, Range("B31")) Is Nothing Then
Call HideRangeB31
ElseIf Not Intersect(target, Range("D31")) Is Nothing Then
Call HideRangeD31
End If
End If
End Sub

Private Sub HideRangeB31()
Rows("32:34").Hidden = True
End Sub

Private Sub HideRangeD31()
Rows("32:34").Hidden = False
End Sub

Thanks in advance for any tips.

 

[John_w]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .CountLarge = 1 Then
            If .Column = 2 Then
                Rows(.Row + 1 & ":" & .Row + 2).Hidden = True
            ElseIf .Column = 4 Then
                Rows(.Row + 1 & ":" & .Row + 2).Hidden = False
            End If
        End If
    End With
End Sub

 

[Bram1212]

Hi John,

Thanks for your reply. I see now that every click on a cell in column B or D will hide/show the 3 rows below it. That is not exactly what I was going for.

My workaround is to have a smarter/cleaner way for the 'group/ungroup' function in Excel without having those + symbols in my sheet.

Therefore I do not need it to work on every cell but on every 4 lines since the three lines below would be optional and therefore I would like to hide/show them when necessary.

So row 31 is the first where this applies and then the clicking function of column B&D need to hide/show row 32:34
Then row 35 same for 36:38
Row 39 40:42
Row 43 44:46

And so on, last row is 123 which then needs to hide/show 124:126.

Hope I am making any sense

 

[dmt32]

Hi,
not fully tested but see if this code will do what you want

Place in your worksheets CODE PAGE

Dim KeyCells        As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Long
    
    If Target.CountLarge > 1 Then Exit Sub
    
    If KeyCells Is Nothing Then
        For r = 31 To 123 Step 4
            If KeyCells Is Nothing Then
                Set KeyCells = Union(Cells(r, 2), Cells(r, 4))
            Else
                Set KeyCells = Union(KeyCells, Cells(r, 2), Cells(r, 4))
            End If
        Next r
    End If
    
    If Not Intersect(Target, KeyCells) Is Nothing Then
        Rows(Target.Row + 1 & ":" & Target.Row + 3).Hidden = Target.Column = 2
    End If
    
End Sub

Note the variable at the top of the code. This must be placed at very TOP of your worksheets code page OUTSIDE any procedure

Dave

 

[RoryA]

This appears to be a duplicate of this other thread: How to assign macro to cells in Excel

Please do not post the same question more than once. This is now closed.