VBA Hide/Unhide 3 rows below active cell

Question

I've got the below code that works well, however I have soo many sub accounts(like "Ads_20_21") that I have to replicate the code many times over and create new named ranges to what is essentially just hiding/unhiding 3 rows below for every sub account. Is there a code that I can assign to a button that will just hide/unhide 3 rows below the active cell, I've tried looking everywhere for help but no luck. Much appreciated for any help.

Sub ToggleHiddenRow(rng As Range)
  With rng.EntireRow
    .Hidden = Not .Hidden
  End With
End Sub

Sub Ads_20_21()
    ToggleHiddenRow ActiveSheet.Range("Advertising_20_21")
End Sub

Variatus Variatus · Accepted Answer · 2020-11-06T01:03:39

I suggest this code:-

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Const TriggerClm        As String = "A"         ' change to suit
    Const FirstDataRow      As Long = 2             ' change to suit
    Const RowsToHide        As Long = 3             ' change to suit
    Dim Rng                 As Range
    
    Set Rng = Range(Cells(FirstDataRow, TriggerClm), Cells(Rows.Count, TriggerClm).End(xlUp))
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        Set Rng = Range(Rows(Target.Row + 1), Rows(Target.Row + RowsToHide + 1))
        Rng.Rows.Hidden = Not Rng.Rows(1).Hidden
        Cancel = True
    End If
End Sub

It's an event procedure that responds to the double-click event, meaning it runs when you double-click a cell. The event will be taken note of only in the code module of the sheet on which you want the action. Therefore it's essential that the procedure is installed in that module and nowhere else. Because of the special connection this module has to what's happening on the worksheet Excel sets up this module when a tab is created. Use the existing module, not one that you insert yourself.

The 3 constants at the top of the code are for you to adjust. Determine the column you want to double-click, the first data row and the number of rows you want to hide/show, starting from the row below the row you double-clicked. The procedure will not run when you double-click another column or above the first data row. When it runs, it will hide the 3 rows if they are visible or unhide them if they are hidden.

I would look for a way for the program to know when a row is clicked that pertains to a subaccount and skip the action for such rows. If you have such a criterium, establish it in code before If Not Application.Intersect(Target, Rng) Is Nothing Then and then include it in that same line. However, as the code is now, there won't be any big punishment for clicking the wrong row. Undoing the action just takes one double-click.

VBA Hide/Unhide 3 rows below active cell

1 Answers