locked
Find the bottom right cell in the selection of cells. RRS feed

  • Question

  • I have been trying to find a way to determine the last cell in a range of selected cells.  So far I have found how to make selections of known cell (in the likes of WorkSheet.Range("B3:F5")) - this is well and all good except when a user makes the selection the only known cell is the top left.  I illustrate this below:

    We can find the first cell as "B3" but the user has selected up to "F5".  How can I find this cell?  Ultimately I would like to either merge this selection or change the font style to bold.  I cannot do this without knowing the range selection.  I have spent several days searching how to do this but to no avail!  I am writing in Visual Basic.

    TIA
    HotIndigo


    Sunday, May 24, 2020 9:10 PM

Answers

  • To:  HotIndigo

    Couple of ways...

    Sub WhereIsIt()
     Dim rng As Excel.Range
     Dim N As Long
     Set rng = Excel.Selection
     N = rng.Cells.Count
     VBA.MsgBox "Bottom right corner is " & rng(N).Address(False, False) & "   "
    End Sub
    '---
    Sub WhereAlternate()
     VBA.MsgBox Excel.Selection.Address(False, False)
    End Sub
    '---

    Free Excel programs at MediaFire (no ads)...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    • Marked as answer by HotIndigo Monday, May 25, 2020 10:52 PM
    Monday, May 25, 2020 9:02 PM

All replies

  • Hi,
    This is a sample.


    Sub Last_RowCol()
        ' --- numeric expression
        MsgBox "Last Row = " & Selection.Row + Selection.Rows.Count - 1 & Chr(13) _
                & "Last Col.= " & Selection.Column + Selection.Columns.Count - 1
        ' --- alphabet expression
        MsgBox "Last Cell = " & Replace(Selection(Selection.Count).Address, "$", "")
    End Sub

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Monday, May 25, 2020 7:33 AM add more
    Monday, May 25, 2020 12:29 AM
  • Hi Ashidacchi, I have seen the usage of  Selection  before but I have not found a definition for it.  When I tried it, I get a  'Selection' not declared error.  How is selection defined?

    TIA
    HotIndigo.

    Monday, May 25, 2020 7:37 AM
  • Hi HotIndigo,

    If you wrote the code I had provided, please provide the version of your Excel.
    (mine is Excel 2016 32-bit)

    Regards,

    Ashidacchi -- http://hokusosha.com

    Monday, May 25, 2020 7:41 AM
  • Hi HotIndigo,

    What do you think about this code? It returns last row and column. Then it converts column number to letter:

    Sub BottomRightCell()
        LastRow = Selection.Row + Selection.Rows.Count - 1
        LastColumn = Selection.Column + Selection.Columns.Count - 1
    
        If LastColumn >= 1 And LastColumn <= 16384 Then
            iA = Int((LastColumn - 1) / 26)
            fA = Int(IIf(iA - 1 > 0, (iA - 1) / 26, 0))
            ColumnLetter = IIf(fA > 0, Chr(fA + 64), "") & _
                            IIf(iA - fA * 26 > 0, _
                            Chr(iA - fA * 26 + 64), "") & _
                            Chr(LastColumn - iA * 26 + 64)
            MsgBox ColumnLetter & LastRow
        End If
    End Sub




    • Edited by tdecker85 Monday, May 25, 2020 10:04 AM
    Monday, May 25, 2020 10:02 AM
  • Hi Ashidacchi,

    I am using Excel Professional 2010 - 32 bit.

    All the best,
    HotIndigo.

    Monday, May 25, 2020 12:42 PM
  • Do you mean something like the code below.

    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

    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

    I hope this helps!

    Monday, May 25, 2020 12:55 PM
  • HI RhodesG,

    Looks good but unfortunately it presupposes a range.  When a user selects a range we don't know what they have selected, hence the original question - how do I find this range that has been selected.

    All the best,
    HotIndigo.

    Monday, May 25, 2020 1:05 PM
  • To:  HotIndigo

    Couple of ways...

    Sub WhereIsIt()
     Dim rng As Excel.Range
     Dim N As Long
     Set rng = Excel.Selection
     N = rng.Cells.Count
     VBA.MsgBox "Bottom right corner is " & rng(N).Address(False, False) & "   "
    End Sub
    '---
    Sub WhereAlternate()
     VBA.MsgBox Excel.Selection.Address(False, False)
    End Sub
    '---

    Free Excel programs at MediaFire (no ads)...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    • Marked as answer by HotIndigo Monday, May 25, 2020 10:52 PM
    Monday, May 25, 2020 9:02 PM
  • Many thanks,  the trick was Excel.Selection.  Problem solved - good pointers and have adapted to my needs.

    All the best
    HotIndigo.


    • Edited by HotIndigo Monday, May 25, 2020 10:56 PM
    Monday, May 25, 2020 10:54 PM
  • Hi HotIndigo,

    Thank you for providing Excel version and architecture.
    I tried to check if my code worked with Excel 2010 32-bit, and found it worked as we expected.


    Regards,

    Ashidacchi -- http://hokusosha.com

    Tuesday, May 26, 2020 12:51 AM