Find last row and column in VBA for a selected area

Question

I am trying to find the last row and the last column of an area I select in the sheet.

typically I use this to find last row and column:

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column

However these lines are for Row = 1 and Column = 1.

let say I have a table elsewhere. I want to select the table and run the code. Then I want the following variables:


FirstRowTable = 
LastRowTable =   
FirstColumnTable = 
LastColumnTable =

I tried somehting like this, but it is not working:

LastRowTable = Selection.Row + Selection.Rows.Count - 1
FirstRowTable = Selection.Row

Any ideas?

What do you mean by "not working"? That code works for me. Please provide a [mcve]. In any event -- such code is often indicative of over-reliance on `Selection`. See [How to avoid using Select in Excel VBA?](https://stackoverflow.com/q/10714251/4996248) — John Coleman, Nov 19 '19 at 11:36

score 4 · Answer 1 · answered Nov 19 '19 at 11:38

4

Here are some of the parameters of Selected rectangular range:

Sub range_reporter()
    Dim r As Range
    Dim s As String
    Dim nLastRow As Long, nLastColumn As Long
    Dim nFirstRow As Long, nFirstColumn As Long

    Set r = Selection

    nLastRow = r.Rows.Count + r.Row - 1
    MsgBox ("last row " & nLastRow)

    nLastColumn = r.Columns.Count + r.Column - 1
    MsgBox ("last column " & nLastColumn)

    nFirstRow = r.Row
    MsgBox ("first row " & nFirstRow)

    nFirstColumn = r.Column
    MsgBox ("first column " & nFirstColumn)

    numrow = r.Rows.Count
    MsgBox ("number of rows " & numrow)

    numcol = r.Columns.Count
    MsgBox ("number of columns " & numcol)

    s = r.Address
    MsgBox ("address " & s)

    s = r(1).Address
    MsgBox ("address of first cell " & s)
    MsgBox ("worksheet " & r.Worksheet.Name)

    MsgBox ("workbook  " & r.Worksheet.Parent.Name)

    MsgBox ("item count " & r.Count)

    '
    'sub-ranges
    '
    MsgBox "address of first row " & r.Rows(1).Address
    MsgBox "address of first column " & r.Columns(1).Address
End Sub

answered Nov 19 '19 at 11:38

Gary's Student

95,722
10
59
99

This is probably useful to OP so +1, but your method of calculating first and last row are identical to OP's way (except that you used a range variable) so it leaves mysterious their claim that it isn't working. My guess is OP has an unrelated bug in their actual code and has misdiagnosed its source. – John Coleman Nov 19 '19 at 11:41
Hi guys, Thank you so much! Very Kind of you. Okay, So I tried all codes, but I still get an error in the "Selection". It says: Compile error: Expected Function or variable. However, I tried to open a new document and implemented the code, and everything was fine. Very strange. – Learning_script Nov 19 '19 at 14:58
@Learning_script Sounds like you might have defined a sub or function called `Selection` in your original code (or something along those line). Also, somewhat phantom bugs can be reduced if you use `Option Explicit` at the top of each module. Are you doing that? – John Coleman Nov 19 '19 at 19:38
oh my god! I am so stupid. I had a code elsewhere, which was called "Selection". I deleted that, and everything is now working fine :) I am still learning! Thank you so much! – Learning_script Nov 20 '19 at 12:31

score 2 · Accepted Answer · answered Nov 19 '19 at 12:15

You may put your selection into Range object and use it's rows and columns properties:

Dim selectionRange As range

Dim firstRow As Long, firstCol As Long, lastRow As Long, lastCol As Long

Set selectionRange = Selection

firstRow = selectionRange.Cells(1, 1).row
firstCol = selectionRange.Cells(1, 1).column
lastRow = selectionRange.Cells(selectionRange.Rows.Count, 1).row
lastCol = selectionRange.Cells(1, selectionRange.Columns.Count).column

It's like you're scaling your worksheet to smaller size. Range.Cells(1,1) will be the upper left cell in your defined or named range and so on just like with worksheet.

score 1 · Answer 3 · answered Nov 19 '19 at 12:07

As an alternative you could use the address property of the range/table:

Sub Test3()

    Dim FirstRow As Long, LastRow As Long
    Dim FirstColumn As Long, LastColumn As Long
    Dim RowCount As Long

    FirstRow = Range(Split(Selection.Address, ":")(0)).Row
    FirstColumn = Range(Split(Selection.Address, ":")(0)).Column
    LastRow = Range(Split(Selection.Address, ":")(1)).Row
    LastColumn = Range(Split(Selection.Address, ":")(1)).Column
    RowCount = Selection.Rows.Count


    'If using a table you can also use:
    'FirstRow = Range(Split(ThisWorkbook.Worksheets("Sheet1").ListObjects("Table6").Range.Address, ":")(0)).Row

    MsgBox "First Row: " & FirstRow & vbCr & _
           "Last Row: " & LastRow & vbCr & _
           "First Column: " & FirstColumn & vbCr & _
           "Last Column: " & LastColumn & vbCr & _
           "Row Count: " & RowCount

End Sub

This will fail if the selection is a single cell as it won't be able to split the address string by the :.

Edit - but as @JohnColeman mentioned, this doesn't solve why your code doesn't work.

Find last row and column in VBA for a selected area

3 Answers3