I am using CreateObject in Access to rummage around in an Excel worksheet for data I am copying into the database

How can I determine what the bounds of the occupied area of the worksheet are? I see objects showing the bounds of active areas are, but nothing that shows the full range of used cells. I need these vaules to limit the for/next loops that are doing the rummaging.

The UsedRange property of the Worksheet object returns a
Range object with the adaptly named Used Range.

I believe you can access this property.

HTH.
-----Original Message-----
I am using CreateObject in Access to rummage around in an
Excel worksheet for data I am copying into the database.

How can I determine what the bounds of the occupied area
of the worksheet are? I see objects showing the bounds of
active areas are, but nothing that shows the full range of
used cells. I need these vaules to limit the for/next
loops that are doing the rummaging.
.

Ian,
You can try something like this in a code window for the selected sheet.
The message box will show you the beginning and ending cells that are used
on the worksheet.

Sub UsedCells1()
ActiveSheet.UsedRange.Select
MsgBox UsedRange.Address
End Sub

HTH
Chris Hoffman

"Ian Smith" wrote in message
...
I am using CreateObject in Access to rummage around in an Excel worksheet
for data I am copying into the database.

How can I determine what the bounds of the occupied area of the worksheet
are? I see objects showing the bounds of active areas are, but nothing that
shows the full range of used cells. I need these vaules to limit the
for/next loops that are doing the rummaging.

Interesting. I was looking at the UsedRange object in Access' debug window and didn't see anything relevant. Looking again now, it doesn't admit to an address property, but by explicitly asking for the address, I get what I was after. Thanks a bunch.

I use

Worksheet.UsedRange.HasArray

If Worksheet.UsedRange.HasArray = true the

--
Message posted from http://www.ExcelForum.com