Coincidentally, yesterday a friend sent me a (xl2003) where I had the same
problem resetting the usedrange.

I tried using macros and manually and it just wouldn't let go of that last used
cell. I removed formatting, filters. Deleted rows and columns and it all
failed.

I closed excel and reopened and the the last cell was reset--a hangover from
earlier versions????

But when I have those hard-to-fix worksheets, I'll sometimes just give up and
move/copy the data/formulas to a new sheet and delete the old one.

That usually works--but it can be painful if there are lots of things to restore
(names/headers/filters/...).

Gord Dibben wrote:

That's the problem Dave.

The usedrange is continually being increased.

But all efforts to reset usedrange using conventional means has failed.

CTRL + End still goes to A1048576

Gord

On Fri, 22 Jan 2010 19:09:39 -0600, Dave Peterson
wrote:

A longgggg time ago (xl97???), I wanted to hide columns that weren't used. So I
did the same kind of thing. I'd hide/unhide columns like that code.

And each time the code ran (IIRC), the used range was changed.

I didn't test the code, but it could be doing the same with the rows.

(Adding a "msgbox me.usedrange.address" may show what's changing.)

I discarded my routine after noticing this. It wasn't worth it.

Gord Dibben wrote:

I tested your code on a new completely blank worksheet.

Before adding your code saved workbook size was 9kb

Changed the number in U1 several times, saving workbook in between changes.

Workbook grew to 2.8MB after a few saves.

I unhid all rows and deleted all rows. Removed code from worksheet.

Saved workbook and is now 2.50MB

Cannot get it lower by any means available including Debra's reset usedrange
code.

Can anyone else see what's going on?

I'm at a loss.

Gord

On Fri, 22 Jan 2010 15:06:01 -0800, New2Macros
wrote:

I have had to save it a lot and keep backups to fall back on if it suddenly
expands in size. I deleted rows and colums that were not being hidden by
Visual Basic. However, nothing has been changed in Visual Basic for a year,
and it has been working fine until now.

Here is the string I'm using to hide rows that are being unused:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$U$1" Then
Range("2:" & Rows.Count).EntireRow.Hidden = False
Range(CStr(3 + 2 * Range("U1").Value) & ":" & _
Rows.Count).EntireRow.Hidden = True
End If
End Sub


I just plug a number into cell U1, and it hides the correct cells.

Thanks



"Gord Dibben" wrote:

I have not yet seen confirmation that you saved the workbook after deleting
unused rows and columns on all worksheets.

I cannot see the "string in Visual Basic" so not easy to trouble-shoot that
aspect.


Gord

On Fri, 22 Jan 2010 12:12:07 -0800, New2Macros
wrote:

Still can't find it. I only have a string in Visual Basic - I tried to stay
away from macros. All that string is doing is cutting off rows based on a
certain cell's value.

Thanks


"Gord Dibben" wrote:

Should read defined range

On Fri, 22 Jan 2010 11:13:07 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

As far as the size jump..............change the macro code to copy data etc.
to a defined rather.

.


.


--

Dave Peterson