Convert Private Sub, Double Click, to Sub

J

J. Stephen

New member
Joined
Mar 31, 2012
Messages
6
Reaction score
0
Points
0
Hello,

I came upon this nifty script that hides / unhides groups of empty Rows, which is triggered when double clicked on a cell.

Is it possible to modify this such that it no longer triggers via double click, but from the Active Cell, initiated by a macro?

I will be assigning this to a button and would need the code to be a Sub event , instead of Private Sub.. (I think?)


Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim f As Long
i = Target.End(xlDown).Row - 1
If Target.Column = 1 Then
    Cancel = True
    If Range("A" & Target.Row + 1).Rows.Hidden Then
        For i = Target.Row + 1 To 65536
            If Range("A" & i).Rows.Hidden = False Then
            Exit For
            End If
        Next i
    Range("A" & Target.Row & ":A" & i).Rows.Hidden = False
    Else
        Range("A" & Target.Row + 1 & ":A" & i).Rows.Hidden = True
    End If
End If
End Sub


Any help would be greatly appreciated!

Thank you
 
Hi Stephen,

This is untested, but I believe it should work for you:

Code: 
Public Sub ToggleRowVis()
Dim i As Long
With Selection
    i = .End(xlDown).Row - 1
    If .Column = 1 Then
        Cancel = True
        If Range("A" & .Row + 1).Rows.Hidden Then
            For i = .Row + 1 To 65536
                If Range("A" & i).Rows.Hidden = False Then
                Exit For
                End If
            Next i
        Range("A" & .Row & ":A" & i).Rows.Hidden = False
        Else
            Range("A" & .Row + 1 & ":A" & i).Rows.Hidden = True
        End If
    End If
End With
End Sub

You'd want to throw that in a standard module.
 
Thanks, Ken

Worked great!

Thank you very, very much!

J
 
You must log in or register to reply here.
Back
Top