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 RepliesI 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 have xls file size 50MB
I want to reduce size kindly give me some tips to reduce size.
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.
View 3 Replies View RelatedIs 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 RelatedI 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.
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 RelatedHave 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?
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
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?
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] .....
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.
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.
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])
i have data in 1 worksheet say sheet1 which i copy to another sheet say sheet2. i do some processing like using some formulas on the data from columns A to D and getting an output in column E in the same sheet. Now the problem is, before i start the calculations in sheet2, the file size is 400 kB... and after the calculations are done, it becomes 20 mB... i dont know why this happens... there are some things which i think might be a reason but not very sure...
1. i calculate the last row in column A using
lastrow = Range("A65536").End(xlUp).row
i tried Range("A500") and it seemed fine... Now u may tell me that i can just use 500 and make things simpler but its just a workaround and not a solution...
2. i use macros.. this is just an info...
3. Another thing is, if i use 500, the number of active rows is 500... i dont know whether the right word is active rows but wat im trying to say is, the scroll bar for the rows movement reaches row 500 when it reaches the bottom.
I used to have cells in the extremes of my worksheet filled which resulted in the scroll bars being stretched very thin. Since then, I am only using the top-left part of the worksheet but it is difficult to scroll through since the scroll bars are still stretched. How do I bring them back to normal?
View 5 Replies View RelatedI 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] ........
I have a string containing the addresses of a range of rows.
an possible example: "7:62,63:168,169:270,310:318,319:345,346:350,351:355,360:366"
i would like a sub/ function that will somehow be able to combine/reduce this down to:
"7:270, 310:355,360:366"
would anyone have any good ideas as to how this can be achieved?
I am a big fan of huge files (30MB+) all interlinked with array formulas ...and lots of them. The problem is...one calculation take literally 5 hours. (I have one file I try to calculate since cca 4 hours now...it's at 63%)
I keep getting extra memory with no visible improvement on the processing time. Is there a trick out there that I can use? Something is clearly wrong.
I feel I will soon have to give up Excel, or my formulas, or my job ...
I have a spreadsheet with 34K rows. I would like to reduce to only the rows that include a certain word.
View 5 Replies View RelatedThe spreadsheet contains 3000 lines and the scroll bar has enlarged to as long as the whole spreadsheet.
When trying to scroll down it takes 0.001 second to scroll way down to bottom line.
It's too fast.
Tried deleting empty cells by Control "-". The spreadsheet either freezes/crashes/ or just get worse and the time it takes to scroll does not change.
I have a table that constantly refreshes itself.
[ProductTable]
Product Quantity (live)
P1 10
[AnotherTable1]
Product Quantity Field_3 ... Field_n
P1 =vlookup("P1", ProductTable, 2, FALSE)
The lookup should perform once to find the cell of interest. Subsequent value update should not trigger additional scans. Is this case? If not, how do I cut down the time on the look up?
Let's take the following dataset:
View 9 Replies View RelatedI got a CD with pricing and (high resolution) pictures (for printing). I've built the search/display macros for displaying a page on the net, so all I want to do is reduce the pics (Adobe Photoshop) to save memory - easy, right? Why can't I get the pics out of excel for work in Photoshop? I've done the tools>options>General tab>ignore other applications (clear) thing. I've done the "shift>edit>copy picture" or "right-click>copy" thing - but I can't paste anything anywhere. What's up? I didn't think I was this bad, but I guess I am; life is humbling.
View 4 Replies View RelatedI have a column with numbers in about 500 rows. The entries are 5 numbers long and others 8. So I thought i could use one of the following: A macro code to tell a cell to delete the first 3 numbers if the entry is 8 numbers long?
OR
A macro code to tell a cell to reduce itself to 5 digits long starting from the right? Attached is a small example
When we sell things here at a 'sale' price there is always an argument as to who's department needs to give the discount off.
Each job we do has a labour time and an hourly labour rate.
Each part has a profit in it.
So if something is normally £100 but we are going so sell it for £80 we need tio split the £20 reduction evenly to both departments based on the GP each department has in the sale.
See attached : GP Reduction per department.xlsx
I have been building a spread sheet data base which I think is pretty simple.
It now has quite a few formula's and is taking time to calculate when I add data.
I have tried to simplify some formula's and get rid of complex one's that can be replaced with simple ones... Is there any advice or suggestions for reducing the calculating time..?
I have the following code in a User form and I was wondering if there was a way to reduce the number of lines that are highlighted, but obviously still get the required results!
Private Sub cbSearch_Click()
Dim Product As Range
Set Product = Sheets("Master").Range("A1:A300").Find(Val(tbLookFor), lookat:=xlWhole, LookIn:=xlValues)
If Product Is Nothing Then
MsgBox "Incorrect Product code, try again"
Else
'Populate the userform with data
tbData1 = Product.Offset(0, 1) 'Down 1 row, over 1 column
tbData2 = Product.Offset(0, 2)
tbData3 = Product.Offset(0, 3)
tbData4 = Product.Offset(0, 4)
tbData5 = Product.Offset(0, 6)
tbData6 = Product.Offset(0, 7)
tbData7 = Product.Offset(0, 8)
tbData8 = Product.Offset(0, 9)
tbData9 = Product.Offset(0, 10)
tbData10 = Product.Offset(0, 11)
tbData11 = Product.Offset(0, 12).....................
I need to take every 4th data point from an array of several hundred (col A) and place the reduced array in a new column (col B). I initially tried approaching this in VBA and then tried excel, but to keep it simple i won't show the code that didn't work.
Col A Col B
5.001 5.001
5.002 5.802
5.001 5.951
5.003
5.802
5.805
5.801
5.804
6.951
6.950
6.952
6.951