CODEWITHSUNDEEP
excelvba
Tornado168
Tornado168

Reputation: 455

vba-Last row and column of used range

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.

Upvotes: 0

Views: 4293

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 1

Related Questions