HELP PLEASE - New Workbook is 35MB instead of 35KB!!

  • Thread starter Thread starter Tricia Henwood
  • Start date Start date
T

Tricia Henwood

I am using Excel 2000 (9.0.6926 SP-3) on XP Pro operating system. Today I
created a new workbook - it consists of seven worksheets, each containing 4
or 5 columns of figures - maximum size of any one worksheet is 55 lines -
there are no links to any other information. Suddenly things slowed right
down - when I checked in Windows explorer I discovered that the size of this
workbook is 35,024 kb - I couldn't work out why that would be so I tried
cutting and pasting the information into a new workbook - this time the
result is 35,013 kb. Can anyone explain what is happening here? I have
another similar workbook that I created last week and it's 20kb (which is
what I expected this one would be). I'm afraid I have no way of working out
what is going on here. I'm in a standalone environment. I do NOT have a
virus (I run NAV and all my virus definitions are up to date).

Any help would be very grateful for any suggestions.

Tricia
 
Hi Tricia,
Check your last cell address Ctrl+End which should be at the
intersection of the last used row and column.

If you try to hide a calculation in an out of the way place on a spreadsheet
that can be very expensive in terms of space.

If your last cell is a problem there are two macros that might help you.

Reset All Lastcells in the Active Workbook (#resetall)
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

Making the activecell the LastCell (#MakeLastCell)
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

If you have never used macros before you can get started with
Getting Started with Macros (getstarted.htm).
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you have excess unused sheets you can delete them.

If your last cell is IV65536 then suspect that you used some prograrm
or macro on your worksheets for some kind of conversion. Macros generally
should be only working within the used cell range. You can format the
entire worksheet, but as soon as you put a value or formula into a cell
outside the used range you have enlarged the used range.
you.

BTW when you determine the reason and cause of the problem, please
report back what you found.
 
DId you copy or move the complete sheet in onother Excel book ? Or do you
copy and paste the cells only? If you copy the entire sheet, you are copying
the problem too, so I recommend you copy and paste some cells into new sheet
and book, of new Excel file. Then save the file and review the size, after
that copy other group of cells, save and review the file size again. In this
way, you could detect where is te problem.
Last year, I had a similar problem. In my case, the worksheet had some blank
modules, but nobody knew how the modules installed into the file. To view if
your file has modules, open Tools menu / Macro / Visual Basic Editor.
I hope this information helps you.
Regards,
Patty
 
Patricia Rios said:
DId you copy or move the complete sheet in onother Excel book ? Or do you
copy and paste the cells only? If you copy the entire sheet, you are copying
the problem too, so I recommend you copy and paste some cells into new sheet
and book, of new Excel file. Then save the file and review the size, after
that copy other group of cells, save and review the file size again. In this
way, you could detect where is te problem.
Last year, I had a similar problem. In my case, the worksheet had some blank
modules, but nobody knew how the modules installed into the file. To view if
your file has modules, open Tools menu / Macro / Visual Basic Editor.
I hope this information helps you.
Regards,
Patty

containing
Click to expand...
My thanks to both David and Patty for their replies. Briefly, I did as you
suggested David and discovered that on each sheet of my workbook (using Ctrl
+ End to locate the last cell address) that last cell address was in column
IV and the cell number basically corresponded to the last line of data I had
entered (i.e. row 52, 18, 20, 4, 15 and 30). I have no idea how this
happened, although when I retrace what I did, I actually pasted the original
information from a table I had created in Word. I do recall that there was
some formatting that I couldn't seem to get rid of so I went to
Format|Autoformat| and selected the option above 'None' which basically gave
me no formatting. I then selected the first column and formatted it for
Date|Custom and entered 'dd mmmm yyyy' (because the figures I am working
with cover a period of more than ten years so I need to know exactly when
certain amounts were paid) - I also formatted two other columns for currency
(UK£) - apart from this I can't think of any macros I might have used. The
last column I have used on any sheet is 'F' so I'm at a complete loss to
understand how column 'IV' turns up.

I'm afraid you're right Patty, I always choose the lazy way of hitting Ctrl
+ A to select the entire sheet then Ctrl + C to cut and Ctrl + V to paste so
I can see that I obviously replicated my error in the first workbook by
copying the entire sheets to another workbook.

It's getting past my bedtime now so I'll have another go in the morning to
see if I can start from scratch with a new workbook and just copy and paste
the cells.

My thanks again to your both for your help.

Regards
Tricia

PS David, I've never used a Macro - (at least, now knowingly :-)
 
Hi David,

Just to let you know that I laboriously cut and pasted all the information
into a new workbook by selecting just the cells containing the information.
Voila, I now have a workbook that is a very slim 46Kb instead of 35Mb.
Many thanks for your assistance.

Cheers
Tricia
 
Hi Tricia,
I suggested using macros, which between learning how to use the
macro, installing them, and using them would probably have been
faster than cutting and pasting. Since you did this all to a new workbook
you still have the old one. Make a copy of the old workbook and
try the macros on the copy, just so you can do things faster next time
and there will be a next time.

It would be best to install the macros in your personal.xls workbook
so the macros are available from any workbook.
So when reading about macros in the references I supplied note where
personal.xls is mentioned.

I don't know for sure that learning to use macros and then using them
would have been faster than cut and paste but it certainly would be
once you've started using macros. But the main thing with macros
is that you can use them over and over again.

In your reply to Patty, who suggested cut (copy) and paste, it looks like
you never actually found the cause of the problem. -- If you insert
rows then delete them you still have the high watermark lastused cell.
Don't know how you got over to column IV because it is unlikely that
you inserted out to there and weren't aware of it.

As for not knowing what macros to use -- I gave you two macro, either one
would work. I provided the same link for both -- I corrected the first
one in my previous reply which can see still included with this reply.
The one that does the entire workbook, resetall, would be faster for you,
if it does the job. The MakeLastCell macro would work for sure.
With the macros the change would have occurred in the same workbook
and you would have not need to redo any formatting of cells.
 
Back
Top