Finding the number of rows on a worksheet

[Jonathan]

Hello,

I am writing a VBA procedure in MS-Access 2002 to read rows from an Excel
worksheet and copy them to an Access table. Is there a way to know the total
number of populated rows without reading through all of them and looking for
an end of range marker such as an empty cell? That's what I'm doing
currently. Unfortunately some populated rows have empty cells.



Thanks in advance

 

[andrew.taylor]

Depending on the format of your worksheet, and on exactly what you mean
by
"populated", you might be able to use something like
Application.CountA ("A:A") or ActiveWorksheet.UsedRange.Rows.Count

Andrew Taylor

 

[Guest]

You can give this a try...

msgbox sheet1.cells(rows.count, "A").end(xlUp).row

This is better than used range which can be problematic...

 

[Tom Ogilvy]

Range("A1").CurrentRegion.Rows.count

to test if this is suitable, click in A1 or any cell in the data and do
Ctlr+shift+8 and see if all your data is selected.

 

[Jake Marx]

Jonathan,

Here's a generic function that will return the last used row (given a column
number) for a particular worksheet:

Public Function glGetLastRow(Optional rwsTarget As Worksheet _
= Nothing, Optional rnColNum As Integer = 1) As Long
If rwsTarget Is Nothing Then Set rwsTarget = ActiveSheet
With rwsTarget
glGetLastRow = .Cells(.Rows.Count, rnColNum).End(xlUp).Row
End With
End Function

If you don't pass in a worksheet reference, it will work off of the active
sheet; if you don't pass in a column number, it will use column A when
calculating the last used row. There are alternatives (such as Tom's
solution) that will work better for situations that may have scattered data
where you want the last row with data *anywhere* in it.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Jonathan]

Thank you all for your responses. Tom's solution works for my application
but I am filing them all away for future reference. Thanks again for your
input

-Jonathan