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

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

.

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

.

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

.



.

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

.



.

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


-----Original Message-----
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

.



.



.