VBA req'd so double-clicking cell A1 will perform Control+End keyboard shortcut

[Ironman]

Hello

I'm new to VBA and I wondered if it would be possible for a few lines of VBA so when I double click cell A1 in a specific sheet then it will jump to the bottom of the sheet i.e. the same as performing the Control+End keyboard shortcut.

Many thanks!

 

[Michael M]

Maybe this way...pasted in the worksheet module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("A1").SpecialCells(xlCellTypeLastCell).Select
End If
End Sub

 

[Ironman]

Many thanks Michael!

Although it works, it's also scrolling across to column G, which is the first empty column - would you be kind enough to amend your code to it scrolls directly down to Column A please?

Thanks again!

P.S. Apologies, I've just realised that that is what Ctrl+End does, but it's not quite what I wanted.

 

[Michael M]

OK, but your request was to replicate CTRL + End, which my code does.
To get the last used row in the column, use

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
End If
End Sub

 

[Ironman]

Yes, I just realised that, as per the PS.

Thanks ever so much Michael, it's perfect now!

 

[Michael M]

Glad to help...?

 

[Peter_SSs]

Just a minor suggestion ..
Since it is not possible to double-click more than 1 cell at a time, there is really no need to go through the 'Not Intersect' process - you can be more direct.

If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Address = "$A$1" Then

 

[Ironman]

Thanks Peter.

 

[Peter_SSs]

Thanks Peter.