False UsedRange in hidden columns

[Sandy-V]

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]

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]

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

 

[Charles Williams]

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]

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

 

[Charles Williams]

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]

Hi Charles,

I haven't yet recreated the usedrange extending to row
65536 in hidden rows (fortunately). But as you mentioned
the possibility I will look out for it.

I'm slowly starting to find some consistency in the way
the usedrange changes with hidden rows & columns, no doubt
following the path many others have trod.

One thing I've noticed, if a wb is saved before hiding
rows/cols, then rows or cols are hidden outside the
usedrange, the usedrange remains the same. In other words
the UR does not then extend into hidden rows or columns
with this method. Bit of a mystery!

Regards,
Sandy

savituk yahoo co uk