VBA: Optimal way to select Top Left cell of selection

Question

Trying to find the best way to select the top left cell from a selection. The current code looks too bulky to be the optimal way:

Sub CAIShowHandle()

    Dim TopLeftColumn As String
    Dim TopLeftRow As String

    'changing to only top left cell
    With Selection
        TopLeftRow = .Row
        TopLeftColumn = Col_Letter(.Column)
    End With
    Range(TopLeftColumn & TopLeftRow).Select

End Sub

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Is there a better way to write this?

`Range("A1:XCD1000").Cells(1, 1)` will give you `Range("A1")`, In your case: `Selection.Cells(1, 1).Select` would do it. — Damian, Oct 18 '19 at 09:47

score 4 · Accepted Answer · answered Oct 18 '19 at 09:51

4

All you need to do when you have a selection:

Selection(1).Select

However, be carefull using .Select as it can mostly be avoided. See this post on StackOverflow for more clarification on that subject.

answered Oct 18 '19 at 09:51

JvdV

70,606
8
39
70

2

So simple! Worked a treat! Thanks – SamHarper Oct 18 '19 at 09:58

VBA: Optimal way to select Top Left cell of selection

1 Answers1