How to resize selection until first sheet column-CodePudding

I simply need to resize my selection from selected range to first sheet column (basically need to select everything on the left). Let's assume the selection is C3:E5 then I want to extend my selection to A3:E5. Let's assume, again, the selection is AE12:AF34 then I want to extend my selection to A12:AF34.

If Not SearchRange Is Nothing Then
                SearchRange.Offset(0, -1).Select
                Range(Selection.Offset(640, Selection.End(xlToLeft)), Selection.Offset(0, 0)).Select
                                            'What to do here ^^^
End If

Note: if possible, I prefer to avoid using select, accordingly to this guide.

CodePudding user response：

You can select from the left column to the first (A:A), using the next approach:

  If Not searchRange Is Nothing Then Range(searchRange.Offset(0, -1), cells(searchRange.row, 1)).Select

And up to the first empty cell:

 If Not searchRange Is Nothing Then Range(searchRange, cells(searchRange.row, searchRange.End(xlToLeft))).Select

CodePudding user response：

Reference From First Column

The function references the rectangle range from the first worksheet column to the last column of a given range. Also supports non-contiguous ranges (multi-ranges).

Sub RefFromFirstColumnTEST()
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range: Set rg = ws.Range("C3:F5,AE12:AF34")
    Dim ffcrg As Range: Set ffcrg = RefFromFirstColumn(rg)
    Debug.Print ffcrg.Address(0, 0) ' Result: 'A3:F5,A12:AF34'
End Sub

Function RefFromFirstColumn(ByVal mrg As Range) As Range
    If mrg Is Nothing Then Exit Function ' Multi-Range
    Dim trg As Range ' Total Range
    Dim arg As Range ' Area Range
    For Each arg In mrg.Areas
        If trg Is Nothing Then
            Set trg = arg.Offset(, 1 - arg.Column) _
                .Resize(, arg.Columns.Count   arg.Column - 1)
        Else
            Set trg = Union(trg, arg.Offset(, 1 - arg.Column) _
                .Resize(, arg.Columns.Count   arg.Column - 1))
        End If
    Next arg
    Set RefFromFirstColumn = trg
End Function