Get row number of lowest/largest row number containing data?

Question

Possible Duplicate:
How can I find last row that contains data in the excel sheet with a macro?
Error Finding Last Used cell In VBA

If I have a spreadsheet and the largest row number which has data is 10,434 is there a function which will give me the row number 10,434?

Thank you

That question doesn't look related- unless i am after usedrange? — intrigued_66, Jul 04 '12 at 13:57
The duplicate posted is also a duplicate and therefore an incorrect reference. — Fionnuala, Jul 04 '12 at 14:12
@Remou: Yes the duplicate posted is a duplicate but gives you the complete answer unlike the original :) — Siddharth Rout, Jul 04 '12 at 14:14
I think you will find there are two parts to the reference I posted. — Fionnuala, Jul 04 '12 at 14:16
@Remou: Yup. I saw that and the second is incorrect as well. — Siddharth Rout, Jul 04 '12 at 14:19
I was hoping there was an inbuilt vba function- rather than assuming 65536 rows etc (and as i'm on Excel 2007 that would definitely not work) — intrigued_66, Jul 04 '12 at 14:26
@Porcupine: Did you see my answer in the link that I gave? That will work in any version of Excel — Siddharth Rout, Jul 04 '12 at 14:27

score 0 · Answer 1 · edited Jul 04 '12 at 14:18

0

Sub largestrow()
Dim largestrowNum As Long
Dim col As Integer
For col = 1 To Columns.Count
lastrow = Application.WorksheetFunction.Max(Cells(65536, col).End(xlUp).Row, largestrowNum)
Next col
MsgBox "Largest row number =" & largestrowNum
End Sub

(will work as long as you have less than 65536 rows)

edited Jul 04 '12 at 14:18

brettdj

54,857
16
114
177

answered Jul 04 '12 at 14:04

Nullbeans

309
1
8

This code uses the 65536 row limit of xl2003. A more robust version is `Cells(Rows.Count, col)`. But this still fails in this example for a blank column – brettdj Jul 04 '12 at 14:19

Get row number of lowest/largest row number containing data?

1 Answers1