Hi Sandy,
I think it has something to do with having all rows down to and including
the last row hidden: if you hide rows 61 to 65535 I think you find that the
last used row is now 65535, but if you hide rows 1000 to 65536 the last used
row is 999.
So it probably depends on exactly how excel internally handles tracking cell
data.
To make usedrange reset things properly you may have to explicitly reset
cell properties to their defaults: its a bit of a black art figuring out
what needs to be done.
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
"Sandy V" wrote in message
...
Hi Charles,
Thanks for the extra info. That explains why my used range
in hidden columns blocked off to the right extends to col
256.
But with the same logic, if say rows 61 to 65536 are
similarly hidden why doesn't the used range also extend to
row 65536 ?
I'm very pleased it doesn't! but would be interested to
know why not.
Sorry but one more Q. In most scenarios applying
.UsedRange correctly resets. However even after unhiding
everything in my original sheet it does not; need to save,
close and reopen. Clutching at straws but is there
any "trick" to reset the UR in code.
Thanks and regards,
Sandy
savit yahoo co uk
-----Original Message-----
Hi Sandy,
The used range always includes cells that need any
information about them to
be stored, and hidden cells is info that needs to be
stored.
But GoTo only ever goes to the last Visible cell (ie
ignores hidden cells on
the edge of the used range).
So its not possible to reset the used range to exclude
hidden cells.
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
"Sandy V" wrote in
message
...
Thanks Dave, for the comment and suggestion.
I knew that resetting the UsedRange in code was not
reliable, however I had thought it was always reset when
saving the WB. But it seems not necessarily if large
sections are hidden - irritating!
Looping only visible cells in the type of sheet I
described is a good idea. Trouble is working out whether
it's a "blocked off" sheet, or one with just some hidden
row/col's which otherwise I would still need to check.
Thanks again,
Sandy
savituk yahoo co uk
-----Original Message-----
I think it's a hit and miss proposition. A while
back, I
had more misses than
hits with hidden columns. So I don't depend on it to
work.)
But maybe as an alternative, you could just loop
through
the visible cells in
the used range:
dim myCell as range
for each mycell in _
activesheet.usedrange.cells.specialcells
(xlcelltypevisible).cells
next mycell
Sandy-V wrote:
I've inherited a worksheet that has hidden columns &
rows
to the right and below the visible area of 60 rows x
10
col's, size of a printable page.
My code loops each cell in the UsedRange, only 600
cells
so should be quick. However the Usedrange count is
226816
cells (886 x 256), and very slow.
If I unhide rows & columns, Usedrange.count
decreases to
15360 (60 x 256). So, simply by unhiding, UsedRange
in
code can now reset the Usedrange to exclude rows 61
to
886, but not columns 11 to 256. Actually I'm not
even
sure it's my code resetting, if I goto LastCell
before
running any code it selects R60C256.
If I now save it in unhidden state and reopen,
Usedrange.count then correctly reports 600,
indicating
there had not been any leftover formats etc in the
previously hidden columns.
So, is there any way I can fully reset the Usedrange
but
without unhiding R's & C's, saving and reopening the
WB.
TIA,
Sandy
--
Dave Peterson
.
.