I have a spreadsheet that refuses to reset its last used cell even
though I delete all rows between the last row that i want to have in the
usedrange and the last row. Then i go to cell A1 and save the workbook.

While this usually works, in a particular worksheet I am using, this
does not work. Any idea why?

Hi
have you checked: http://www.contextures.com/xlfaqApp.html#Unused

--
Regards
Frank Kabel
Frankfurt, Germany

"R Avery" schrieb im Newsbeitrag
...
I have a spreadsheet that refuses to reset its last used cell even
though I delete all rows between the last row that i want to have in
the
usedrange and the last row. Then i go to cell A1 and save the
workbook.

While this usually works, in a particular worksheet I am using, this
does not work. Any idea why?

That is precisely what I did. It did not work. Any other suggestions?

Did you try saving, closing and reopening?

What version of xl are you using?

In some earlier versions, you had to do more stuff.

R Avery wrote:

That is precisely what I did. It did not work. Any other suggestions?

--

Dave Peterson

XL 2002

Yes i have tried saving, closing, reopening. No effect. Any other idea?

Is the last cell in the last column? Any hidden rows/cols,
even within what you think should be the UR ?

Peter

-----Original Message-----
XL 2002

Yes i have tried saving, closing, reopening. No effect.
Any other idea?
.

I've had a few (very few) worksheets that just won't reset this last used cell.

If it's really important to me, I copy the range I want to a new worksheet. But
it has to be really important--to go through page setup, range names, and all
the other stuff.



R Avery wrote:

XL 2002

Yes i have tried saving, closing, reopening. No effect. Any other idea?

--

Dave Peterson

Yes, it was in the last column. Plus, it thinks the last cell is
L65535, and the last row of actual data is in cell L500.

No hidden rows or columns. I tried selecting all cells, copying, and
pasting into new workbook. Even after I did that, I could not reset the
UR. I tried selecting only my data and pasting into new workbook.
After I did this, I extended the used range by putting some value in a
cell far below my data. I tried to reset the UR here, and it worked.

So there is something special about those empty rows. I just can't
figure out what.


Peter T wrote:
Is the last cell in the last column? Any hidden rows/cols,
even within what you think should be the UR ?

Peter


-----Original Message-----
XL 2002

Yes i have tried saving, closing, reopening. No effect.

Any other idea?

.

It's probably innocuous and not worth spending time over,
as Dave suggested. But a real pain if you want to loop the
Usedrange or copy the sheet using the cells method.

A possibility is that at some stage in the sheet's history
some form of formatting was applied to all rows and
columns (height or width perhaps). Not sure if this will
work but try copying rows & columns from a clean and empty
sheet to below and to the right of where you think the
used range should be.

Run Activesheet.usedrange in code, and save/reopen.

Regards,
Peter

-----Original Message-----
Yes, it was in the last column. Plus, it thinks the last
cell is
L65535, and the last row of actual data is in cell L500.

No hidden rows or columns. I tried selecting all cells,
copying, and
pasting into new workbook. Even after I did that, I
could not reset the
UR. I tried selecting only my data and pasting into
new workbook.
After I did this, I extended the used range by putting
some value in a
cell far below my data. I tried to reset the UR here,
and it worked.

So there is something special about those empty rows. I
just can't
figure out what.


Peter T wrote:
Is the last cell in the last column? Any hidden
rows/cols,
even within what you think should be the UR ?

Peter


-----Original Message-----
XL 2002

Yes i have tried saving, closing, reopening. No
effect.

Any other idea?

.


.