Excel VBA return sheet's top cell address based on a range

Question

I was trying to get the top cell address based on a range. "Top cell" here means the cells in the first row of the sheet (row number should always be 1). If the range contains multiple columns, use the first column of the range.

My code is as below:

Sub main()

    Dim rg As Range

    Set rg = Range(Cells(10, 2), Cells(100, 2))

    Debug.Print Range(rg.Cells(1, 1).EntireColumn.Address).Rows(1).Address(RowAbsolute:=False)
    ' Out put is $B1
End Sub

It works as desired. But I was wondering if there's a simpler way/expression to achieve the same. Thank you!

Sorry, I misread your question. Try Intersect(Rows(1), rg (1, 1).EntireColumn).Address (False) — DaveU

paul bica paul bica · Accepted Answer · 2018-05-20T18:08:43

Use Cells(1, rg.Column)

Option Explicit

Public Sub GetTopCell()

    Dim rg As Range

    With ActiveSheet

        Set rg = .Range(.Cells(10, 2), .Cells(100, 9))

        Debug.Print .Cells(1, rg.Column).Address(RowAbsolute:=False)    'Prints $B1

    End With

End Sub

rg.Column extracts the column of the top-left cell in the defined range

Excel VBA return sheet's top cell address based on a range

1 Answers