File Size Jump

[New2Macros]

I can't figure out why my Excel file size is jumping from 550kb to 35mb. I
am running some formulas to copy data, and some special links from other
pages, as well as scripts which hides rows... I tried deleting extra
unhidden rows, and got nothing. Any ideas?

 

[Gord Dibben]

After deleting the extra rows and columns you must SAVE the workbook to
effect changes in used range.

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

Sounds like you are copying to a greater range than you need.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Should read defined range

 

[New2Macros]

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]

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

 

[New2Macros]

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]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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/...).

 

[New2Macros]

So if I move the data to a new sheet - how do I make sure this doesn't happen
again? Should I discontinue using this code? I like only seeing cells that
are being utilized...

And yes, this sheet is using a lot of formulas and links, etc., and shifting
it would take hours...

 

[Gord Dibben]

I am interested in knowing what criterion is used for the number you place
in U1

How do you decide to enter 42 or 978?


Gord

 

[Dave Peterson]

I wouldn't use that code.

So if I move the data to a new sheet - how do I make sure this doesn't happen
again? Should I discontinue using this code? I like only seeing cells that
are being utilized...

And yes, this sheet is using a lot of formulas and links, etc., and shifting
it would take hours...

 

[New2Macros]

Well, we just thought it made the sheets look clean, and you couldn't enter
data in cells that weren't being utilized. We use this spreadsheet for our
equipment list, so we enter a number in Q1 based off of how many devices will
be listed on this sheet.

All the sheets are linked to a "master sheet" which is what we print out,
and you don't want someone to enter data on a line in the other tabs that
isn't set to be printed.

I hope that makes sense...

 

[Dave Peterson]

I still wouldn't use the macro.

Well, we just thought it made the sheets look clean, and you couldn't enter
data in cells that weren't being utilized. We use this spreadsheet for our
equipment list, so we enter a number in Q1 based off of how many devices will
be listed on this sheet.

All the sheets are linked to a "master sheet" which is what we print out,
and you don't want someone to enter data on a line in the other tabs that
isn't set to be printed.

I hope that makes sense...