Determine used range of worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...
Excel worksheet for data I am copying into the database.
How can I determine what the bounds of the occupied area
Click to expand...
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 said:
I am using CreateObject in Access to rummage around in an Excel worksheet
Click to expand...
for data I am copying into the database.
How can I determine what the bounds of the occupied area of the worksheet
Click to expand...
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.
 
Back
Top