Used Range is not resetting

R

R Avery

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?
 
D

Dave Peterson

Did you try saving, closing and reopening?

What version of xl are you using?

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

Peter T

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

Peter
 
D

Dave Peterson

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

R Avery

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.
 
P

Peter T

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
 
Top