Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 20th 09, 09:59 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 4
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

I have a very large wotkbook 10MB and want to find out how I can reduce it
and also find out the size of each worksheet within the workbook to see if
there is anything I can do to reduce the size of each worksheet. I use many
vlookup , if formauls in the workbook

  #2   Report Post  
Old January 20th 09, 11:50 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

A 10MB file for Excel isn't very large, It is just Large. Excel often makes
workbooks larger than necessary. Sometimes making a small change will cause
excel to grow from a 1 MB file to a 10 MB file. It is difficult to return it
back to a smaller file when you have a lot of formulas. I been successful in
returning a workbook to a smaller size by copying each sheet one at a time to
a new workbook. This won't work if you have a lot of formulas that reference
multiple worksheets.

The key is what is really your problem. Is your problem that the workbook
is slow or take a long time to open and close? You can turn automatic
updating off to speed up the entering of a workbook. You can convert some of
your formulars to macros which will speed up operations. I don't think that
finding the SIZE of the worksheets will really solve any problems.

"keyson2004" wrote:

I have a very large wotkbook 10MB and want to find out how I can reduce it
and also find out the size of each worksheet within the workbook to see if
there is anything I can do to reduce the size of each worksheet. I use many
vlookup , if formauls in the workbook

  #3   Report Post  
Old January 20th 09, 12:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 968
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

You can find out the size of each worksheet by deleting a worksheet and
saving the workbook using a different name. Do this in turn until you only
have 1 worksheet left. The difference in the file size gives you the size of
the worksheet you just deleted.
Note that the disk size of a worksheet does not neccessarily correspond well
to the memory used by that worksheet.

But I agree with Joel that the size of a worksheet is not usually the
problem, but calculation time and memory is.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Joel" wrote in message
...
A 10MB file for Excel isn't very large, It is just Large. Excel often
makes
workbooks larger than necessary. Sometimes making a small change will
cause
excel to grow from a 1 MB file to a 10 MB file. It is difficult to return
it
back to a smaller file when you have a lot of formulas. I been successful
in
returning a workbook to a smaller size by copying each sheet one at a time
to
a new workbook. This won't work if you have a lot of formulas that
reference
multiple worksheets.

The key is what is really your problem. Is your problem that the workbook
is slow or take a long time to open and close? You can turn automatic
updating off to speed up the entering of a workbook. You can convert some
of
your formulars to macros which will speed up operations. I don't think
that
finding the SIZE of the worksheets will really solve any problems.

"keyson2004" wrote:

I have a very large wotkbook 10MB and want to find out how I can reduce
it
and also find out the size of each worksheet within the workbook to see
if
there is anything I can do to reduce the size of each worksheet. I use
many
vlookup , if formauls in the workbook



  #4   Report Post  
Old January 20th 09, 01:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

You could copy each worksheet to a new workbook (each one to a separate
workbook), save those workbooks, then use windows explorer to look at the size
of those new workbooks.



keyson2004 wrote:

I have a very large wotkbook 10MB and want to find out how I can reduce it
and also find out the size of each worksheet within the workbook to see if
there is anything I can do to reduce the size of each worksheet. I use many
vlookup , if formauls in the workbook


--

Dave Peterson
  #5   Report Post  
Old January 15th 14, 02:20 PM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 1
Default

Quote:
Originally Posted by keyson2004 View Post
I have a very large wotkbook 10MB and want to find out how I can reduce it
and also find out the size of each worksheet within the workbook to see if
there is anything I can do to reduce the size of each worksheet. I use many
vlookup , if formauls in the workbook

I have had this problem. A common cause of a bloated workbook is the creation of a lot of empty rows at the bottom of one sheet. For example a reformat that selects all 100,000+ rows in a column might do this.

My solution: Go to each worksheet. Go to the last desired row (you can even add a buffer. Select all cells or all rows beneath that and delete the rows. Often when you get to the offending sheet you get a pop-up warning that there is not enough memory, but you can continue as long as you agree that the "undo" won't be possible. In a recent case after I cleaned up the offending worksheet, my file shrank from 40MB to 0.276MB (an excel 2007 file).

This was much faster than trying to keep resaving the 40MB file after a sheet is deleted.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find a worksheet by its name in a workbook? Alchemist Excel Discussion (Misc queries) 2 January 15th 09 01:17 AM
SIZE Window, Tile, Worksheet, Workbook, Whatever it's called. JamesInNeedFootballInDeed New Users to Excel 4 October 31st 07 11:16 PM
How to evaluate the size of each worksheet in a workbook? [email protected] Excel Discussion (Misc queries) 1 August 22nd 07 11:50 AM
Find & Replace a link to another worksheet in the same workbook Sidesfive Excel Worksheet Functions 1 July 6th 06 10:23 PM
Find a value in a workbook and return the worksheet name Craig Excel Discussion (Misc queries) 2 August 10th 05 09:47 PM


All times are GMT +1. The time now is 03:38 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017