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:
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?
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?
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.
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?
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.
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
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?
I am working with 4 seperate workbooks where 4 different people enter data into cells. I have a 5th workbook that needs to have all the data from the 4 individual workbooks bought across so I can see all data. Like a master file. basically, in each cell of the master, I have the following code. =IF('[loans - 0708 - officer 01.xls]07-08'!B5="","",'[loans - 0708 - officer 01.xls]07-08'!B5)
I then have the code for each officer. As there are 24000 lines collectively for the financial year, the size of my master is 37mb, even when its empty, obviously cause each cell still have a formular in it. Is there ANY way to copy across the data from other workbooks without having to have every single correpsonding cell have this kind of formular to view the code? Also, copy it without having to open the other workbooks.
I have an excel file which consist of 12 sheets. The main sheet contain columns from A to EL and 556 rows.
At the moment, i have data on from column A to AI (we will add more data day by day). All data in main sheet are linking to another 4 sheets which basically using IF, VLOOKUP, and Conditional Formatting. My file originally was only 4.6MB in size.
However, after adding the formula (IF & Vlookup) and conditional formatting to the fourth sheet (number of columns is from A to BZ) then the file size increased from 4.6MB to 13.7MB.
My excel file also became very slow and i need to take off the Automatically Calculation option.
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.
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.
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.
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.
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 reposting this because I do not think I did a very good job explaining what I am trying to do.
I am at a total loss on how (if it is even possible) of how to do this. So what I have done is tryed to break down each step. If someone could even get me started in the right direction many of the steps are redundant and I could work on that part myself.
I am attempt to create system to match parts that are alike in a single project, so I can create a material ordering list. This is just one step in the process (the hardest one) I will take the returned data and use it further in the process to create the actual material list. I have 2 worksheet of Data "PartsNeeded" and "PartsAvailable" with a 3rd sheet "PartsFilled" as my report Sheet.
Attach is the sample data along with a "notes tab"
I'm creating a Purchase Order Form that will reduce time spent adding in contact details.What I'm looking to achieve is a form that will be printed with a few formulas allowing sections of the form to be filled out automatically once a singular company name (chosen from a drop-down list I created, currently with a 'Combo box ActiveX') then the rest of the form is filled out accordingly.
At the moment it's a bit of a mess, not too sure where I'm meant to put the ranges.There are currently 2 sheets - Sheet 1 with the form, Sheet 2 with all contact information.
i would like to rank a column so that when entering a number in a cell it will move up the column so that the greater number falls down the colum and the larger rises up the column
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
I am looking for macro that when run, will open a file explorer window and prompt the user to select a folder and file where they have data stored. Then I need it to be stored as a variable and used as a part of a "Workbook.Open Filename" command.
The reason for this is that, I have a huge formatting marco stored within a workbook. When a user extracts a report from SAP, I want the workbook to grab the file that is extracted, open it and import all of the data in order to be formatted.
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?
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
I would like to use this database list I export from another program to excel format. What I want to do is to change in the C column.
The value under Akt.saldo is how much of a given article we have in stock (saldo). And the Rest. in column B is the reservation, how much of that article thats is going to be shipped at that day. ex 14161 year(14) week(16) day(1).
So as you can see in column C the Rest. value never subtracts from the stock. What is want is something like that below.
Here is just selected all the values under stock and moved to the next column. Then used white color to hide it.
Here i subtract the hidden value with the reservation to be able to see how much is left after that order.
And the same here. Subtracting next order with the current stock.
How do i do this with a macro? The list is changing everyday.
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.
