vba-Last row and column of used range

Question

I'm trying to get the last row and column of a used range.

To get the last row and column I use the code below

Dim varCurrentUsedRange As Variant
Dim LastRow As Long
Dim LastCol As Long

varCurrentUsedRange = ActiveSheet.UsedRange

LastRow = UBound(varCurrentUsedRange )
LastCol = UBound(varCurrentUsedRange , 2)

This code returns exactly the last used row number, even the first used cell is not in Row 1.

But it returns the number of used columns not the last used column.

For example, if the first used column is 2 and last one is 6 it's expected to return 6 but it returns only 5 (number of used column).

So how to get the last used column number regardless the starting column?

Thank you in advance.

Have you seen [This](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) — Siddharth Rout, May 28 '17 at 12:22

score 1 · Accepted Answer · answered May 28 '17 at 12:01

Consider the following:

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

    ActiveSheet.UsedRange
    Set r = ActiveSheet.UsedRange

    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)
 End Sub

It defines some of the properties of UsedRange.
It does not rely on creating an internal VBA array.

Thank you so much @Gary's Student .. Just one comment on your code.. the line nFirstRow = r.Row always return 1 even if the first used row is 2 or 3.... While nFirstColumn = r.Column returns exactly the number of the first used column. — user3286479, May 28 '17 at 12:11

vba-Last row and column of used range

1 Answers1