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.

ADVERTISEMENT

Reduce Row Range In Existing Worksheet To Reduce File Size

Dec 17, 2013

I have a problem where I extended a formula down to over 40,000 records which has increased the file size substantially. I only need it to scroll down to a few thousand rows now that I realized that there is alot less data to populate the worksheet. Is there any way to get it back to a scroll range that is more modest in size?

View 1 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

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

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

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

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

Store Formulas In VBA In Order To Reduce File Size

Nov 14, 2011

Have a file of 7 mb having 100 columns and unlimited rows (user defined- approx 500). At all these cells in that area, I refresh (copy/paste) the same formula for all cells, read as:

[=IF($DF47=0,"",IF(ISERROR(MATCH(HE$45,$Q$22:$Q$26,0)),INDEX($DF$12:$HE$31,
MATCH($DF47,$DF$12:$DF$31,0),1+HE$11-$DF$11),INDEX($Q$22:$S$26,
MATCH(HE$45,$Q$22:$Q$26,0),IF($DA47=GROUPSHIP,2,3)))) ].

Refresh is necessary bcs I insert/delete/sort rows.

In order to reduce file size and instead of saving all these formulas into the approx 5000 cells, I would prefer if I could have a function in VBA and call it (with copy/paste) with a smaller formula at all these 5000 cells as: [= myfunction], so as to have this simple formula in each of 5000 cells, hoping that this will reduce size.

How can I write the long formula above to a function in VBA?

View 2 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

Reduce Size Of VBA And Add 4 Rows

Jan 12, 2014

I have a working VBA Module that needs to be reduced. If you notice I have copied it 5 total times to get it work the right way.

Code:

Sub TestingSub()

Dim wsSource As Worksheet
Dim wsDest As Worksheet
Set wsSource = Worksheets("Catering and Rental Worksheet")
Set wsDest = Worksheets("Catering BEO")

'Entertainment Sub Box Fill
wsSource.Range("D13").Copy
wsDest.Range("F17").PasteSpecial xlPasteValues
Application.CutCopyMode = False

[Code] .....

View 3 Replies View Related

Reduce Memory Size Of Excel Workbook?

Jul 11, 2012

if there are any simple steps that can be taken to reduce the size of an Excel workbook short of deleting information. I have an Excel file which is taking to long to open and save. The file size is approx 5,450kb.

The file cotains Macros, Formulae, Named Ranges and Colour formatting.

View 1 Replies View Related

Will VBA In Place Of A Function Reduce This Workbook Size

Mar 5, 2009

I know that VBA doesn't always slim down workbook size and calculations are calculations whether in VBA or Formula format; but in this instance, do you think VBA would be a better choice? This workbook is currently enormous in size and slow, and I think I can slim it down if I used the right VBA procedures. However, Im not certain what those procedures would be.

On two seperate worksheets in a workbook, I have a column that contains a formula that references a third sheet.

Sheet 1 = "Order"
Sheet 2 = "Turn-in"
Sheet 3 = "Bank"

Ok, here's the scenario: On the "Order" sheet, I have a list of orders from my company; on the "turn-In" sheet, a list of turn-ins. The "Bank" sheet lists ALL transactions (ORDER AND TURN-IN).

On both the turn-in and Order sheets, the formula is pretty much the same; it references a different column on the bank sheet, but does it the exact same way. Ok... The Order sheet lists all order from the company by order number (column A); the formula uses that order number and finds the transaction on the "Bank" sheet and returns the order cost as the result. however, the order numbers on the bank sheet have a 5 digit prefix on them (the prefix is the same on all orders, its used by the bank but only orders with my 5 digit prefix are on the list sent to me and pasted to this sheet. So all orders on bank sheet have the same prefix)

The below formula is in Cell C10 on "Order" Sheet; Column C lists the Cost reported by the bank. The formula below references the Bank sheet database, looks for an order number that matches the order number in A10 (Adding the prefix of course), and returns a cooresponding cost for that transaction.


=IF(ISBLANK(A10),"",IF(COUNTIF(BANK!$A$4:$W$10001,("ECGGT"&A10))>1,"",SUMIF(BANK!$A$4:$W$10001,"ECGGT"&$A10,BANK!$F$4)))

I know this seems long for what it is, but it has to add the prefix, and then match the order number, and reference the cooresponding cost.

The kicker, this forumla is in more than 10,000 cells; 6,000 on the order sheet, and 5,000 on the turn-in sheet. Of course the references are a little different, but the principle is the same for all of them.

Is there a way to do this in VBA to save space and resources? This formula is really dragging.

View 9 Replies View Related

How To Reduce Formula Size/improve Efficiency

Sep 1, 2009

how to reduce formula size/improve excel efficiency?

I have a really complicated formula with lots of COUNTAs, VLOOKUPs, IFs, etc etc etc which returns a variety of values, ZEROs (for blank cells being referenced), and #NAs.

I would like to turn all ZEROs and #NAs into ""s without repeating this super long formula over and over.

I am curently using:

=IF(or(ISBLANK([superlongformula]),ISNA([superlongformula])),"",[superlongformula])

View 9 Replies View Related

Reduce Number Of Times Opening And Closing Blank Source File

Jul 9, 2012

I have code in VBA that takes a source Excel file and parses it to multiple re-formatted worksheets. Each formatted worksheet is different depending on a variable in the source. There are n of each type of data (1:n Water Sources, 1:n Water Treatment, 1:n Water Storage). Each item needs a copy of a blank re-formatted worksheet appended to the end of the existing data-type worksheet. For example: Water Source #2 is appended to the bottom of Water Source #1 on the same worksheet.

Currently, I am opening the blank re-formatted workbook each time an additional variable of same type is found in the source file. Meaning, if there are 8 Water Sources, I am opening and closing the blank re-formatted workbook 7 times to copy a blank Water Source to the bottom of the Water Source worksheet and then populate the data. AND if there are 3 Water Treatment, I would open and close the blank formatted workbook 2 more times to copy a blank Water Treatment to the bottom of the Water Treatment worksheet. The same goes for Water Treatment.

Obviously, a huge waste of resources and performance. I would rather open the blank re-formatted workbook once (first time an additional worksheet is needed), use the worksheets as needed, and close it at the end.

Attached is my current, working code of the above.

How should I re-write it to check if the blank workbook is already open use it, if not open it?

Code:
Sub Add_Facility(ByVal Worksheet_Name, Copy_Range, Max_Limit)
Dim Last_Row As Integer
Dim Source_Range As Range
Dim Dest_Range As Range
Dim wkb As Workbook

Set wkb = ActiveWorkbook

[Code] ........

View 5 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

Fatal Error: Allowed Memory Size Of 33554432 Bytes Exhausted, Saving Without Macros 2007

Jun 16, 2009

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155. I have following macro, what worked just fine in 2003. Now I'm working with 2007 and I can't set up the Microsoft Visual Basic for Applications Extensibility library. Here's the

View 2 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

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

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

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

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

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 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

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

File Size Made Smaller

Apr 20, 2006

Is there a way of eliminating un-used rows & columns to bring your file size down.

I already know about eliminating unnecessary pages, and I've cleaned up defunct modules, etc., and anything else that's just clutter.

The Row & Column thing would be a big help.

View 7 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

File Has Grown In Size Exponentially

Mar 21, 2008

I have a spread sheet with a macro attached to it, approximately 500 lines of code and 3 sheets approximately 30 rows by 30 columns. it recently shot up from 130 KB to 15,000KB. Do I need to rebuild this thing from scratch or is there a way of finding where the fat is? I have tried clearing all columns beyond my active table area and all rows below my active area.

View 4 Replies View Related

Generate File List With Name / Date / Size

Jun 10, 2013

I have a folder containing about 100,000 files and subfolders. Approximately 20% of the files are duplicates. I would like to create a list of all the files with some details so that I can sort through and identify which ones are duplicates, then create a batch file to delete them. How to get a file list with additional details, like size, modified date, etc, into an excel format?

View 3 Replies View Related

Saved File Size Not Reduced To Original

Apr 26, 2009

I have an Excell 2003 workbook with 6 worksheets and some VBA programmed macros in it.

With a small amount of data the .xls file size is 150k. Then I tested it with a much larger sample of data, which goes into one of the worksheets, resulting size 3MB. Later on I reverted to reading in the original small amount of data but the file size stubbornly sticks to 400k instead of reverting to 150k.

The old data is definitely not there anymore - I used this code to clear it:

View 13 Replies View Related

'Background' Insertion File Size/limiting?

May 15, 2006

I'd like to insert a picture background for multiple s/ sheets I've developed, however, even though the images I'm using are only 30kb's in size, it bumps my s/sheet file size from 900kb's to over 4mb's ! Is there a way I can stop this from happening, or, any way to restrict the background image to only a certain part of the worksheet as opposed to the entire thing?

View 3 Replies View Related

With Sheet Background, File Size Is Huge

Apr 1, 2007

Is there anyway to put a background on a worksheet, without the file size of the workbook becoming monstrously huge? I have a small jpg file of our company logo, and I think that it would be a nice touch for it to be the background to our spreadsheet. But when I use this 50k file as the background, the workbook suddenly grows to a size of 7MB.

View 4 Replies View Related

ADVERTISEMENT