How to resize selection until first sheet column

Question

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.

It is unclear what you want to do. Let's assume the selection is `C3:E5`. What should be selected (referenced): `A3:B5`, `A3:C5`, `A3:E5`? — VBasic2008, Jun 06 '22 at 07:00
What do you mean by "first sheet column"? Column A:A, or first empty cell to the right on the respective row? Should `SearchRange` be a single cell, or more? — FaneDuru, Jun 06 '22 at 07:00
@VBasic2008 I edited the question, I hope it's more clear now. — Dovatti, Jun 06 '22 at 07:25

FaneDuru · Accepted Answer · 2022-06-06T07:46:07.120

1

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

edited Jun 06 '22 at 07:46

answered Jun 06 '22 at 07:26

FaneDuru

38,298
4
19
27

1

@Dovatti The above answer selects from a column to left, like in your code... In order to select the last col of `searchRange` inclusive, you should eliminate the `Offset` part. I could see now your edit and what you need... :) I edited the above answer and adapt it to return as you required in the question edit... – FaneDuru Jun 06 '22 at 07:29
1

Yep, I've already corrected your code block according to my needs :) thanks by the way, it worked great. – Dovatti Jun 06 '22 at 07:31

score 1 · Answer 2 · answered Jun 06 '22 at 08:17

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

How to resize selection until first sheet column

2 Answers2

Reference From First Column