File (with Macro) Keeps Growing In Size?

Jan 14, 2010

I have a workbook that grows in size on every use. I import a text file, manipulate the data, manually export it and delete the data from the workbook.
So why does it continue to grow in size? By manipulating I mean, add rows and text to the new rows. Attached is a copy of the workbook in native form (have not ran the macro) and a sample file to import, the excel file is 59kb in size and the test file is 9kb in size. I have seen my original file grow to over 500kb from repeating the same macro on the same file many times or running many different files for testing.

There is a CommandButton on Sheet1, it will open a userform. Click in the largest textbox and it will open a file dialog box in "C:". Place the supplied text file in "C:" or some other folder of your choice as you can navigate to any folder or drive from the dialog box. The macros that add lines and or text are "JumpToLabelName" (this runs first) and "InsertRows" which runs second. These two macros do all the work.

Take note:
I store information in the Registry, "GeoMeasure" under "VB and VBA Program Settings" (Normal windows key entry). You can search for "GeoMeasure" and delete it if you decide to test run the file.

View 5 Replies


ADVERTISEMENT

Workbook Growing In File Size

Aug 12, 2008

I have a workbook that I am working on that seems to be having file size growth issues.

All it is is 2 worksheets.

Worksheet 1- Columns A-S & 63 rows. That worksheet alone is 7mb.

no macros..No VBA..No formulas. Just straight text data with a handfull of auto-filters. I took the auto filters off and saved to see if that was the issue and it did not make a difference.

Worksheet 2- Columns A-S & 35 rows. This worksheet is almost 3mb.

Now I can make a change (when my computer catches up) to 2 cells and sometimes it will jump in size by 1mb or more.

View 9 Replies View Related

Shared File Growing Massively

Jun 5, 2007

I have been reading up about shared workbooks within here which quite frankly is annoying me! I have rebuilt a spreadsheet from yesterday and shared it with:

Dont Keep change history
Update changes when file is saved
The changes being saved win

At start of day the file is 660kb, now by lunchtime I have it as 15mb. Does anyone know why this occurs? Becuase the file is getting bigger this is affecting performance

View 9 Replies View Related

Minimize Macro File Size

Dec 18, 2007

I have an excel file that is about 1,400 kB in size. This file is the backend for a complex model with many inputs, and I now want to build a front end for it using userforms and VBA. However, when i created the first userform, my excel filesize blew out to about 7,000 kB. When I added all of my userforms (about 16 of them because there are lots of inputs, organised into categories), the filesize grew to 77,000 kB.

I have tried everything to reduce file size, including clearing all unused cells, avoiding pivot tables of any sort, and have also exported then re-imported all of my VBA modules and userforms. But the situtation stays the same: no userforms or VBA and my filesize is about 1,400kB, add in all the userforms, and filesize is 77,000 kB. Is there a way to keep file size down with userforms, or is that just the way it is? Is the only way to keep file size down to have a single userform, all questions on that one form, perhaps with visible and invisible controls depending on the 'page' of questions being viewed?

View 7 Replies View Related

Minimize File Size Of Macro-enable Workbook?

Sep 9, 2012

I have a macro-enabled workbook, in sheet1 i have two columns Country and Codes, they are in column C and D respectively. I used vlookup formula in generating the codes of the countries pasted in column C, they are being looked-up in sheet2 in column C and D as well. I already have 100000+ rows in sheet 2 under column C and D that's why the file is now heavy. Is it possible to encode column C and D in sheet 2 but still can be looked-up? or is there any other way to minimize the file size.

View 7 Replies View Related

Reduce File Size Using Macro To Perform 500,000 Calculations

Dec 10, 2008

I have this formula that checks a line of values to see whether it is within the constraints of a separate list of values.

=if(AND(Data1!A4="count1",Data1!D4>="bin1",Data1!D4

View 9 Replies View Related

Workbook Containing Macro To Reduce File Size Locks Up When Shared

May 28, 2008

I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at [url]
Option Explicit

Sub ExcelDiet()

Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ColFormula As Range
Dim RowFormula As Range
Dim ColValue As Range
Dim RowValue As Range
Dim Shp As Shape
Dim ws As Worksheet

Application. ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next

This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?

View 6 Replies View Related

Excel 2013 :: File Size Grows When Using Macro To Copy / Paste To New Workbook

May 9, 2014

I'm using Excel 2013 on Windows 7.

I have an Excel file which has a macro that sucks in data from a number of separate files (two Excel, a varying number of csv). The macro grabs all the individual files and loads them into one of three worksheets with some formatting, sorting, and structuring on the way. All good so far.

I then need to take two of the worksheets and copy them to a new Workbook which I can send out to some users. So I use the Workbooks.Add method, and use the Selection.Copy on just the data (UsedRange.Rows/Columns.Count to ensure it is only the data) and Selection.PasteSpecial with the xlPasteColumnWidths, xlPasteValues, and xlPasteFormats options to copy the data over. None of the data is filtered. Just straight data.

I would expect the newly added workbook to be smaller than the one with the macros because the one with the macros has two extra sheets that I don't transfer, and the sheets I do transfer are identical.

And that's where it gets weird. The file with the macros and more data is 18.7Mb, but the new one with only two sheets is 24.8Mb. One is .xlsm and the other is .xlsx (because it has no macros).

I've tried opening the new workbook and saving as .xlsm (no material difference to the size) and as .xls (it got even bigger). I've opened the new workbook, gone to the end of the data and deleted all the blank rows and columns, but no effect (because the rows are already empty).

Why would the file with less in it be so much bigger (or bigger at all)?

View 2 Replies View Related

File Size... Vlookups Resulting In 90MB File

Apr 9, 2007

I have created a monster Excel file that contains loads of vlookups and is 95MB and extremely slow.

I guess that vlookup creates tables in the background, hence the file size. Does anyone have any ideas on how to decrease the file size while keep the vlookup functionality?

View 9 Replies View Related

File Size Too Big?

Sep 22, 2006

I have a workbook which has a file size much bigger than I would expect. To test it I inserted one blank worksheet and deleted everything else. I have removed a macro that was embedded in the workbook. I have removed all protection etc. I would now expect the file to be similar to a new workbook (circa 13k) but it is still 782kb even though there is absolutely nothing in it.

View 5 Replies View Related

How To Reduce File Size

Jun 11, 2014

I built a workbook that is used by our account team to summarize account status. Each worksheet has several cells that use conditional formatting to call attention to the status reported in that cell. Each manager sends their worksheets to a Senior manager who then consolidates into a single workbook containing approximately 50 worksheets. Once consolidated, the file size has grown to over 12 Mb and this needs to be reduced to a more manageable number, preferable 4 Mb or less. Prior to buliding the new version of this workbook, the file size was less than 2 Mb.

I have removed the Macros and believe the size is due to the conditional formatting in each worksheet.

Once the worksheets are compiled, is there a way to remove the conditional formatting and save the existing format - Similar to a "Paste Values" function? The senior manager does not need to change cell value to the formatting of the worksheet he receives could be saved.

To be specific - one cell turns has no color if the value is 10 or less, is yellow if 11 - 15, and Red if 16+. Once sent to the manager, the variance in this cell is not needed; he only needs to see the color associated with the value when sent. Next week he will receive an updated worksheet and a new value will be entered. The conditional formatting is only needed by the individual preparing the original worksheet.

View 3 Replies View Related

Maximum File Size?

Mar 6, 2014

I am creating a statistics database for my personal use. I have been developing this for many, many months now, and I have learned a lot about Excel and its capabilities in the process. However, I am becoming concerned because my files occasionally crash Excel, and I am wondering whether this is because my files are getting too large. Typically, the crashes occur when the main file is calculating (but not always). It stops responding, and I have to force the files closed through Windows Task Manager.

Without getting into the specifics of how the files work (although we can do that if it is necessary), here are the names of the files and their current sizes:

Baseball Database.xlsx -- ~28 MB
Baseball Reports.xlsx -- ~5 MB
Statistics.xlsx -- ~4 MB

I will have all three of these files open simultaneously when I am working on them, and my laptop has 4G of RAM. I do not have much (if any) extraneous formatting in the files -- I know how to remove that -- but there is quite a bit of conditional formatting in the files that I want to maintain. I am considering partitioning (separating) the tabs in the Database file into separate files.

View 3 Replies View Related

Reducing File Size

Sep 19, 2012

I have a spreadsheet which is currently about 10 MB, I would like to reduce this as much as possible.

I am using lots of Vlookups but on each I am only referencing the data I need, there are also quite a few macros.

I paste value all the formula I do not need.

View 5 Replies View Related

Row Height And File Size

Dec 19, 2013

I have an 11mb file with:

5 worksheets
30 columns per worksheet
Total of 1300 rows of data across the 5 tabs

The row heights are not uniform across all worksheets but when I change them all down to 65536 on each sheet to height of 12, the file reduces in size from 11mb to 3mb.

It concerns me as it is almost like data could be getting lost.

View 1 Replies View Related

File Size/Formulas

Jan 10, 2007

I have a workbook that permits the user to enter data in no sense of order. From the original I am working for, there could be over 1500 rows of data. I then created a new worksheet to accept the unordered data to be sorted by a customer and date. I then created 20+ new worksheets within the same workbook, that will accept via an IF statement, the data for each customer. The problem as I see it is as the formula runs, I need to delete the blank rows on each of the 20+ worksheets. This is fine and the customer is happy with the set up. My problem is the file size. With just the formulas/worksheets, it is over 62 MG.

Is there a different formula/function I can use that won't require so much space?

View 9 Replies View Related

Reduce File Size :; 50 MB

Mar 31, 2008

I have xls file size 50MB

I want to reduce size kindly give me some tips to reduce size.

View 9 Replies View Related

Does A File With VBA Grow In Size

Jul 9, 2009

I created a new form for our field personnel to complete daily. They are telling me that over a few weeks the file grows in size. The file size is 335KB starting out end up in a few weeks being 3.5megs. Now they add on some other sheets and things which undoubatbly make the file size larger but I am wondering if there is anyway thet VBA could somehow make the file bigger as the open it each day over a few weeks? They are not adding in any VBA, they are only using the code which is provided, which is accounted for in the 335kb. It seems unlikely but i thought i would ask.

View 9 Replies View Related

File With The Size 20MB

Aug 3, 2009

I created a new excel file with filtering and after i saved the file , i found that the size for this excel file is 19 MB, so is this normal size for an excel file or there is a problem in this file and i have to fix it

View 9 Replies View Related

Reducing File Size

Jul 3, 2006

I don;t know if there is a way around this but we have a Excel workbook that holds around 14 worksheets taht is getting too large. It currently is around 2.5mb and we constantly use it as a template which is completed and emailed. Sometimes we have to email 4 of these all together and so teh email is too large, even zipping them has next to no effect on the size. The sheet that seems to causing the problem is one that has around 500 lines of formula across 20 columns. Most of these fields have conditional formatting and its this taht is boosting the size.

View 6 Replies View Related

File Size Reduction

Jan 12, 2007

I am working on a spreadsheet that pulls data from an external pivot table.

The spreadsheet is 25 columns wide by 72 rows. I have 15 worksheets currently but need to add another 7. The current file size is 22MB and I keep getting a NO MEMORY error message.

I am not using any fancy formulae just references to an external pivot table.

View 9 Replies View Related

File Size - Reduce

Feb 13, 2007

I currently have an excel file with lots of formulas on quite a few pages and these are carried down for so many records - its sort of a template to generate information from/to certain sheets for many other purposes.

- is there any way to compress the file (without actually zipping it) as it must remain a working document?

View 7 Replies View Related

Reduce File Size

Apr 28, 2008

My excel file has grown to 23Mb. It has around 10 worksheets and a similar number of userforms. Because the file is large, it slows down the VBA code when it encounters a save command. Is there any way I can improve this? Do userforms use alot of memory or is it the length of code? There are a few images on the userforms but the images are less than 1 Mb in total when all added together.

View 3 Replies View Related

Minimize Size Of File

Jun 5, 2008

My file is currently about 70,000 mb,consisting of 10 spreadsheets.

Can i decrease the size by combining the spreadsheets?

Also, will nested formula take longer to load compare to 3 different cells referencing upon each other?

list that tells us how much each and different formula affects the size of the file?

View 4 Replies View Related

Insert Picture From File Increases File Size More Than The Picture You Inserting

Mar 17, 2009

I am putting together a price list in excel and using images and hyperlinks to jazz it up a bit - what I am finding is that if I insert a 25KB GIF image, the excel file grows in size by over 100KB

why is this disproportionate growth happening and is there a way around it - I dont want my Price List growing too large but need the images....?

View 9 Replies View Related

Reduce File Size Considerably?

May 15, 2014

Is there a way to reduce the Excel file size considerably? Any quickest and easiest way to reduce file size in excel ...

View 3 Replies View Related

Size Of Excel File Changes Automatically?

Jun 19, 2013

We are using an excel file, which doesn't contain more then 1MB of data and have few "count and sum" formulae. but size of file keeps fluctuating from 1MB to 500MB. some time the size of file would be 1MB and once you will refresh the window file size will go to 480MB or some random number.

because of this excel file is quite slow and very difficult to work.

View 10 Replies View Related

Reduce File Size By Use Of Macros

Jan 8, 2007

I still cant figure out how to write macros. If someone could help me out with this I would appreciate it.

Currently I have a worksheet that is 20mb.

This file contains what takes up most of the space. I repeat this line many times throughout the worksheet.
010807.zip

Once again it is a zero eliminating code.

If someone could reduce the number of formulas for me, I think I could figure out how to adapt it to repeat throughout the sheet.

View 11 Replies View Related

Beating Down File Size With A Big Stick

Jun 13, 2007

I've got a spreadsheet which currently 7.06MB!

The bulk of the file (6MB+) is in one sheet which is a data store.

All headings are on row 1 and are duplicated across 4 times for ease of reading.

Columns A-D have the main data and comprise a total of 39751 lines and a lot of duplication (basically it is 30 items for 25 clients (identical setups) for 53 weeks.

Columns E-H is an average for 4 or 5 weeks (depending on the period) for each items for each client and takes up 9001 rows.

Columns I-L have the quarterly averages (i.e. average of 3 periods) and takes up 3001 rows.

Columns N-P have the data which is displayed on other sheets and takes up 750 rows.

Now, on columns B,F,J and N I have the name of the client in row 2 (for example) and then the formula "=A2" in row 3 and down. This is repeated for each client (29 formulas each).

Is there any way I can batter the size of this sheet down?

View 9 Replies View Related

Keeping File Size Down (2003)

Sep 21, 2007

I am about to undertake a fairly large spreadsheet development project. One of the key requirements is that the size of the spreadsheet be kept to below 4mb, preferrably below 1mb. This is due to an unavoidable restriction.

Although the spreadsheet will not have large volumes of data, it will contain approximately 30 sheets. There are no graphics embedded in the sheet. I imagine there will be a reasonable amount of VBA and/or forumulas in the sheet. Each sheet will basically be a data-entry form with a number of formulas. No sheet will have more than 100 rows.

Without knowing the full details of the spreadsheet, it is difficult for you to give a definite answer as to the size of the sheet, so I am simply looking for some helpful tips in keeping the size down.

Do formulas take up more space than VBA code? When I format cells, should I format the entire row, or just the range? Do features like sheet and cell protection take up a lot of space?

View 9 Replies View Related

Reduce File Size :: Without Zip Function

Nov 14, 2003

Is there a way to reduce the Excel file size considerably? This excel file is huge with many sheets. I do not like to use Zip function.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved