Scribd Logo
Upload

Welcome to Scribd!

  • 252 views

    Excel - VBA Finding Corners of A Selection - Stack Overflow

    Uploaded by

    vaskore
    This document discusses finding the cell addresses of the corners of a rectangular selection in VBA. It provides code to get the top left, top right, bottom left, and bottom right cell addresses and row/column numbers of a selection. The code uses the .Cells, .Rows, .Columns, and .Address properties on the Selection object. It also discusses using Range instead of Selection to avoid issues.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Excel - VBA Finding Corners of A Selection - Stack Overflow

    Uploaded by

    vaskore
    252 views3 pages
    This document discusses finding the cell addresses of the corners of a rectangular selection in VBA. It provides code to get the top left, top right, bottom left, and bottom right cell addresses and row/column numbers of a selection. The code uses the .Cells, .Rows, .Columns, and .Address properties on the Selection object. It also discusses using Range instead of Selection to avoid issues.

    Original Title

    excel - VBA Finding corners of a selection - Stack Overflow

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses finding the cell addresses of the corners of a rectangular selection in VBA. It provides code to get the top left, top right, bottom left, and bottom right cell addresses and row/column numbers of a selection. The code uses the .Cells, .Rows, .Columns, and .Address properties on the Selection object. It also discusses using Range instead of Selection to avoid issues.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    252 views3 pages

    Excel - VBA Finding Corners of A Selection - Stack Overflow

    Uploaded by

    vaskore
    This document discusses finding the cell addresses of the corners of a rectangular selection in VBA. It provides code to get the top left, top right, bottom left, and bottom right cell addresses and row/column numbers of a selection. The code uses the .Cells, .Rows, .Columns, and .Address properties on the Selection object. It also discusses using Range instead of Selection to avoid issues.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 3

    9/26/2020 excel - VBA Finding corners of a selection - Stack Overflow

    VBA Finding corners of a selection


    Asked 3 years, 7 months ago Active 1 month ago Viewed 7k times

    Given a rectangular Selection , how do I find the Cell s for each corner? Specifically, the top right
    and bottom left.
    8
    E.g. if a user selects the range B2:G9 , I would like to get the address of each corner from the
    Selection object.

    I used .Address to get the top left and the bottom right corners, and while I could start splitting the
    strings and perform regex replace on them I want to know if there is a cleaner way.

    excel vba

    asked Feb 14 '17 at 7:31


    Norsk
    572 9 21

    2 Answers Active Oldest Votes

    Do you mean something like the code below.

    9 Note: it's better to stay away from Select and Selection , you could try using With Range("B2:G9")
    instead (not implemented yet in the code below)

    Option Explicit

    https://stackoverflow.com/questions/42220257/vba-finding-corners-of-a-selection 1/3
    9/26/2020 excel - VBA Finding corners of a selection - Stack Overflow
    Sub GetRangeSelectionCorners()

    Dim TopLeft As String, TopRight As String, BottomLeft As String, BottomRight As String


    Dim TopLeftRow As Long, TopLeftCol As Long, BottomRightRow As Long, BottomRightCol As
    Long

    Range("B2:G9").Select
    With Selection
    TopLeft = .Cells(1, 1).Address '<-- top left cell in Selection
    TopRight = .Cells(1, .Columns.Count).Address '<-- top right cell in Selection
    BottomLeft = .Cells(.Rows.Count, 0.1).Address '<-- bottom left cell in selection
    BottomRight = .Cells(.Rows.Count, .Columns.Count).Address '<-- last cell in
    selection (bottom right)

    ' get row and column number


    TopLeftRow = .Cells(1, 1).Row '<-- top left cell's row
    TopLeftCol = .Cells(1, 1).Column '<-- top left cell's column
    BottomRightRow = .Cells(.Rows.Count, .Columns.Count).Row '<-- bottom right cell's
    row
    BottomRightCol = .Cells(.Rows.Count, .Columns.Count).Column '<-- bottom right
    cell's column
    End With

    MsgBox "Top Left cell address is :" & TopLeft & vbCr & _
    "Top Right cell address is :" & TopRight & vbCr & _
    "Bottom Left cell address is :" & BottomLeft & vbCr & _
    "Bottom Right cell address is :" & BottomRight

    MsgBox "Top Left cell's row is : " & TopLeftRow & _


    ", and column is :" & TopLeftCol & vbCr & _
    "Bottom Right cell's row is : " & BottomRightRow & _
    ", Bottom Right cell's column is :" & BottomRightCol

    End Sub

    edited Feb 14 '17 at 7:52 answered Feb 14 '17 at 7:45


    Shai Rado
    31.5k 6 18 42

    3 Why the 0.1 in BottomLeft = .Cells(.Rows.Count, 0.1).Address ? Why not BottomLeft =


    .Cells(.Rows.Count, 1).Address ? – Axel Richter Feb 14 '17 at 7:51

    top_right_row=selection.rows(1).row
    top_right_col=(selection.columns(1).column+selection.columns.count-1)
    2 bottom_left_row=(selection.rows(1).row+selection.rows.count-1)
    bottom_left_col=selection.columns(1).column

    you can get row and column values like this. i havent tested the code if you have any error please
    revert back

    edited Jul 31 at 9:12 answered Feb 14 '17 at 7:44


    Sivaprasath Vadivel
    533 5 12

    https://stackoverflow.com/questions/42220257/vba-finding-corners-of-a-selection 2/3
    9/26/2020 excel - VBA Finding corners of a selection - Stack Overflow

    I like the simplicity of this answer so I gave you my vote. I want to point out that your bottom_right_*
    variables should actually be named bottom_left_* based on the values that are set in them and the
    question being answered. – Ben Jul 23 at 5:20

    1 Thanks for the upvote and I have changed my answer based on your comments – Sivaprasath Vadivel Jul
    31 at 9:12

    https://stackoverflow.com/questions/42220257/vba-finding-corners-of-a-selection 3/3

    You might also like