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

--
Jonathan
KingsKinght1 <at hotmail <dot com

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


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

--
Jonathan
KingsKinght1 <at hotmail <dot com

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...
--
HTH...

Jim Thomlinson


"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

--
Jonathan
KingsKinght1 <at hotmail <dot com

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.

--
Regards,
Tom Ogilvy

"Jonathan" wrote in message
...
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

--
Jonathan
KingsKinght1 <at hotmail <dot com

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

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

"Jonathan" wrote in message
...
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

--
Jonathan
KingsKinght1 <at hotmail <dot com