VBA Hide/Unhide 3 rows below active cell

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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top