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 wrote:
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