F
fern
Hi,
I'm hoping someone can help me reset excel's perception of the 'use
range' I'm using b/c none of the usual tricks have worked (tho the
have in the past). It's pretty much the identical problem to the on
posted by R Avery in May, and I've tried all of the suggestions offere
to him. But since he never wrote back to say what if anything worked
I'm having to post this problem again (see his original post at
http://www.excelforum.com/showthread.php?s=&threadid=223255&goto=nextnewest).
To explain, my dataset runs from A1 to CB603 but the scroll bar extend
down to row 6398 & column DG. I can see that a lot of my formatting ha
been applied to the whole sheet, but when that's happened before (an
resulted in a huge UR), I've been able to fix it pretty easily. Sinc
I've not had any luck, I doubt that that's the cause - or at least tha
alone...
*Of course I've tried ctrl+end (or the variations thereof) to find th
end of the 'used range', and then deleted all the empty cells betwee
that point and my actual dataset.
*I've repeated that using 'clear all' instead of delete - didn't work
*I've also cut & pasted the data range (ONLY) into a secondary workboo
or sheet, but all of the extra empty cells come too.
*I've followed the instructions in the oft-repeated lin
http://www.contextures.com/xlfaqApp.html#Unused, but those don't solv
my problem either (incl the UsedRange macro that's suggested).
*And I've tried using Activesheet.usedrange via VBE (I'll admit that m
VB skills are poor but it isn't exactly a difficult command, right?)
In fact, there's ONLY thing that even remotely explans this (& i
doesn't actually explain anything at all): I've noticed that th
UsedRange resets when I first copy my data range into a new sheet. Bu
once I format the row height back to 13.5 (b/c it reverts to it'
autofit height of 40-280, depending on how much wrapped text the row'
cells contain), the UsedRange jumps back to >6000rows like it was i
the original. Wierd, eh? (Note: you can't blame the cell contents b/
the same data's repeated in lots of other (even bigger) sheets
workbooks with no problems whatsoever)
So does anyone have any ideas for how to solve this puzzle? It's no
destroying my dataset but it does mean that I'm having to try t
maneuver through 600rows of data but only an inch of scroll bar - ver
hard to do all day, every day. So I'd appreciate any help you can give
Thx
I'm hoping someone can help me reset excel's perception of the 'use
range' I'm using b/c none of the usual tricks have worked (tho the
have in the past). It's pretty much the identical problem to the on
posted by R Avery in May, and I've tried all of the suggestions offere
to him. But since he never wrote back to say what if anything worked
I'm having to post this problem again (see his original post at
http://www.excelforum.com/showthread.php?s=&threadid=223255&goto=nextnewest).
To explain, my dataset runs from A1 to CB603 but the scroll bar extend
down to row 6398 & column DG. I can see that a lot of my formatting ha
been applied to the whole sheet, but when that's happened before (an
resulted in a huge UR), I've been able to fix it pretty easily. Sinc
I've not had any luck, I doubt that that's the cause - or at least tha
alone...
*Of course I've tried ctrl+end (or the variations thereof) to find th
end of the 'used range', and then deleted all the empty cells betwee
that point and my actual dataset.
*I've repeated that using 'clear all' instead of delete - didn't work
*I've also cut & pasted the data range (ONLY) into a secondary workboo
or sheet, but all of the extra empty cells come too.
*I've followed the instructions in the oft-repeated lin
http://www.contextures.com/xlfaqApp.html#Unused, but those don't solv
my problem either (incl the UsedRange macro that's suggested).
*And I've tried using Activesheet.usedrange via VBE (I'll admit that m
VB skills are poor but it isn't exactly a difficult command, right?)
In fact, there's ONLY thing that even remotely explans this (& i
doesn't actually explain anything at all): I've noticed that th
UsedRange resets when I first copy my data range into a new sheet. Bu
once I format the row height back to 13.5 (b/c it reverts to it'
autofit height of 40-280, depending on how much wrapped text the row'
cells contain), the UsedRange jumps back to >6000rows like it was i
the original. Wierd, eh? (Note: you can't blame the cell contents b/
the same data's repeated in lots of other (even bigger) sheets
workbooks with no problems whatsoever)
So does anyone have any ideas for how to solve this puzzle? It's no
destroying my dataset but it does mean that I'm having to try t
maneuver through 600rows of data but only an inch of scroll bar - ver
hard to do all day, every day. So I'd appreciate any help you can give
Thx