CODEWITHSUNDEEP
excelrangevba
user2815713
user2815713

Reputation: 29

Last Row in a Range - last row is NOT last used row on entire sheet

I've looked around for how to find the last row in a range, but the answer I found could - at best - only give me the last used row of the entire sheet.

For example, let's say I have elements in A1:A10, B6:B9, and C1:C4. I'd like to find the last row in columns B:C. In this case the answer should be row 9. I've tried using SpecialCells(xlLastCell), but have only gotten an answer of row 10.

I'm sure there's a very easy answer, but I can't find it! Can anyone help?

Upvotes: 0

Views: 906

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

For any range r:

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)

Upvotes: 1

B Hart
B Hart

Reputation: 1118

To get the last row in a specific Range, not necessarily a specific row, you can simply reference the very last cell in that range similar to how you would do it with an array:

Set RR = Range("B6:B9", "C4:C11")
MsgBox "The Last Row is " & RR(RR.Count).Row

OR

Set RR = Range("B6:B11", "C4:C8")
MsgBox "The Last Row is " & RR(RR.Count).Row

Both will return 11 as the last row in the above example.

Upvotes: 0

Netloh
Netloh

Reputation: 4378

Here is an example of how you can return the number of the last row in columns B:C with VBA:

Sub ReturnLastRow()
    MsgBox "Last row in columns B:C is " & _
        WorksheetFunction.Max(ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row, ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row)
End Sub

Upvotes: 1

Related Questions