CODEWITHSUNDEEP
excelvba
Nelda.techspiress
Nelda.techspiress

Reputation: 643

Get last row of specified range in Excel

How do I get the last row of a range that has already been specified and defined and is known to contain the data I want? It seems to me this should be a property of some sort like Range.Row but Range.Row gives the first row number in the range and I want the last row number.

Now the problem is my range doesn't begin in row 1 of the source sheet - it actually begins in row 3 and goes through row 756. Below is the address of my range as viewed by the debugger and it is defined correctly. But since it begins at row 3 using Rows.Count will not work, since it results in 754 instead of 756.

Rows.Count is a reference to the range which is a subset of the original sheet. I want the row number of the last row from the original spreadsheet, exactly as it is in the address below. In other words is there some property on Address that will give me the 756?

transData = transactionsDataRange.Address

: transData : "$A$3:$AP$756" : Variant/String

The address presented in the debugger is correct. All that I want is to extract the row value of the lower right cell in the specified range which would be 756. I can parse the address value and will do that if necessary, but I'm wondering if Excel VBA has a simpler way of achieving this?

Upvotes: 0

Views: 655

Answers (4)

Dy.Lee
Dy.Lee

Reputation: 7567

Specifies the last cell in the range.

Sub test()
    Dim transactionsDataRange As Range
    Dim rngT As Range

    Set transactionsDataRange = Range("$A$3:$AP$756")
    With transactionsDataRange
        Set rngT = .Cells(.Rows.Count, .Columns.Count)
    End With
    MsgBox rngT.Row
End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

Use

Dim LstRow as Long
LstRow = transactionsDataRange.Row + transactionsDataRange.Rows.Count - 1

enter image description here

Upvotes: 2

Vityata
Vityata

Reputation: 43593

Sub TestMe()        
    Dim myRange As Range
    Set myRange = Worksheets(1).Range("D10:D15")
    Debug.Print myRange.Rows.Count + myRange.Row - 1        
End Sub

It prints 15. myRange.Rows.Count is 6 and myRange.Row is 10. Or if you like 1-line-functions, this could be ok:

Sub TestMe()
    Debug.Print LastRowOfRange(Range("D15:E199"))
End Sub

Function LastRowOfRange(myRange) As Long
    LastRowOfRange = myRange.Rows.Count + myRange.Row - 1
End Function

Upvotes: 0

SJR
SJR

Reputation: 23081

An alternative approach

Sub x()

Dim transactionsDataRange As Range

Set transactionsDataRange = Range("$A$3:$AP$756")

MsgBox transactionsDataRange.Rows(transactionsDataRange.Rows.Count).Row

End Sub

Upvotes: 4

Related Questions