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 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.
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.
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'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
If there is either a 1 or 0 i cell A1 what is the most efficient =IF(A1=1;B1;C1) or =A1*B1 + (1-A1)*C1? Or perhaps their are something even more efficient??
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.
To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.
Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
I created a macro that checked a cell for invalid numbers. There were 6 I checked for: #REF, #NUM, ####, #Div/0, #N/A, #Null. Because you can only compare two "phrases" with OR, I had to write it like this:
It is to large to upload here and I have removed the sensitive data that would normally be present. The part I'm currently stuck at is the multiple search criteria, one part works while the other does not. Also, I have yet to discover a way to indicate to the user that the correct Row is selected and being edited in the right cells.
I run a rather simple loop (see below). But as it runs through A1 to A28000 it takes a lot of time. Is there a more efficient loop to operate this simple task?
Assume I have a sheet consisting of 50 rows with 3 columns of figures. A fourth column contains a formula to calculate values, eg A1+B1+C1, on each line/row. I could enter A1+B1+C1 in the first cell and Copy, Paste to the other 49 cells. Alternatively, I replace A1+B1+C1 with a UDF.
Is there any difference in performance with using a UDF compared to using the long-hand/standard approach, or is it just a question of convenience? After all, both are doing the same operation with the same calculation.
I wrote a macro that works fine, although it runs extremely slowly as if plodding along through all of the cells one at a time. I'm sure the computer is faster than that, so I would like it to chug through more quickly.
I am using some syntax that could be optimized ....
Do any of the templates/backgrounds offer the user a white background with every fifth row and column an off-white colour?
My document will be read by LOADS of people, so I want to be able to follow my eye across. A soft system like that lets someone scroll across twenty columns and see they're still on the same row. It's like writing 7 Oct 2013 instead of 07/10/2013. It makes sense whether you use UK dd/mm/yyyy or US mm/dd/yyyy. People say 'October' not 'month no 10.' That's my logic and design principle.
I was working with automatically exporting the contents of a textbox to an image file and found this post which I was able to modify slightly for my purposes.
Is it possible to improve the quality of the output image. I need to print out the resulting image and the quality of the image this method creates is too poor for my purposes.
Attached is a spreadsheet that does exactly what I need it to do but I know that the code can be written better. Perhaps some experts can clue me in to how I could improve.
There is a button on the sheet Mandatory Training that looks at the sheet CS CM and finds certain numbers, when it does it makes them red and copies them to the Mandatory Training sheet. If other lines are added to CS CM that have the same numbers and the button is clicked again, only the new entries are made red and copied to Mandatory Training.
I am generating the chart from the following code but when it prints i.e (Mode#1 or 2 or 3 etc....) with type i.e. (GL_Weld or Bend or any other text). Printing as i.e. (Mode#1Bend or Mode#2GL_Weld). I would like some space in between (Mode#1 Bend) or (Mode#2 GL_Weld) or even put it in next line underneath it ( Mode#1 Bend). How do i incorporate and space or enter next line? Code as follows:
how to improve the following error handling strategy and code samples so as to maximise its usefulness for the purposes described.
During function calculations in VBA, such as is done by numerical integrators, numerical run-time errors may occur, such as: Err.Number = 5 Invalid procedure call eg. LOG(-5) Err.Number = 6 Overflow Err.Number = 11 Division by zero
My present strategy to address these errors:
Say the variable x is used in the function calculations, and some particular value of x causes one of the above errors to occur. After the above errors are trapped, the x value is increased slightly from its initial value, and the failed calculation line is retried (Resume). On the next error trap, the x value is decreased slightly from its initial value, and the calculation tried again. This way, the initial x value is cyclically changed by adding increasing magnitude increments of alternating signs, searching for the nearest x value which will allow the function calculation to be done without error. Thus it causes the x value to swing around the initial x value in a pendulum-like manner, but with a widening swing per pass.
So far, I have the following code:
Code:
ErrorHandler: If Err.Number = 5 Or Err.Number = 6 Or Err.Number = 11 Then If Initial = True Then
i have this macro from this webiste to move every 2nd cell to the row above...(ie B1---after macro---becomes A2) I want this macro to be able to do exactly the same but instead of having a cell, it will have a row...
Code: Sub moveDefs() Dim i, LastRowFrom As Integer 'Identify the last row of the rule set that contains data
I have a large worksheet (Sheet1) containing approximately 15,000 records (15,000 rows x 21 columns). I need to search through all these records, and manually decide whether a record should be copied onto another worksheet (Sheet2) or not. The code I have written works very well (as far as I can tell), but it is extremely slow (the searching takes forever).
The code is as follows:
Sub SearchAndCopy() Dim SearchRow As Long Dim LastRow As Long Dim SearchColumn As Long Dim LastColumn As Long Dim CopyToRow As Long Dim SearchString As String Dim Found As Boolean Dim Response As Integer On Error Goto Err_Execute Found = False 'Fetch Search Term Sheets("Sheet2").Select SearchString = LCase( Range("F3").Text)......................
