Excel Runs Out Of Memory
Jun 10, 2014
I have a macro which filters data on one sheet and copies that data onto 34 new sheets . This works perfectly until the 25th pass when Excel advises that there is insufficient memory to continue. I assumed that this was connected to the clipboard and its ability to remember 24 chunks of data so I turned it off but still Excel gave up at the 25th pass. Is there a way that I can clear the clipboard during the macro's execution or is there something else going on?
View 7 Replies
ADVERTISEMENT
Jan 16, 2014
I am working on a Excel sheet of 156KB. Unfortunately it is increased to around 5MB. May i know the reason that may happened and how can i retrieve that.
View 2 Replies
View Related
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
Jan 27, 2014
I am running Excel 2010 32bit under Windows 7. The Excel file worked perfectly under Excel 2007 and Windows XP. The Excel file is somewhat complex with numerous links and embedded cell formulas. Occasionally, I get the following error message:
5.jpg
Before I install the 64 bit version, I attempted to chack a few other things. I opened the task manager and while viewing the process tab, I opened my Excel file. Once the file loaded, this is the allocation displayed in task manager:
1.jpg
This seemed reasonable considering the size of the Excel file (33Mb). Without changing anything, I clicked save to make sure it was working properly. While viewing the process, I watched the allocation climb, substantially. when it completed, this is what the process displayed:
2.jpg
Continuing to click the save button, without changing anything, continued to increase the memory allocation until the error message was displayed:
3.jpg
4.jpg
Closing Excel and re-openeing the file resets the memory allocation back to normal, which is a popular solution to this issue in the forums.
I did check the same issue using Excel 2007 and the memory allocation releases after each save.
I can see how installing the 64 bit version of Excel can correct this issue, and with 12Gb of Ram, I will probably use this method until I can get this fixed.
View 3 Replies
View Related
Dec 15, 2012
I'm using Excel 2010 and have run into the following problem. I have data in a text editor that I need to separate. I copy and paste this data into Cell A1, then do a "Text to Col" function that separates the data correctly. Now I have other data to paste into other cells on the same sheet. Here's the problem....The "Text to Col" function runs automatically when I paste other data into any other cell on the WS. How do I turn this off without closing the WS?
View 4 Replies
View Related
Jun 20, 2014
On my work computer (Excel 2010, Win 7) when I am stepping through my VBA code using F8 and come to a Workbooks.Open line, the macro automatically kicks in and runs through to the end instead of just stepping to the next line of code. This happens nearly every time but occasionally it works as it should. To prevent this, I have to put a breakpoint on the next line following Workbooks.Open and after that I can step (F8) through the rest of the code. I have never had this problem before and it doesn't happen on my home computer.
View 4 Replies
View Related
Jun 17, 2012
I have a group of Excel (2003) template files, each with a different name. When these files are opened they ask for certain information and save themselves as a unique name. Within these files are VBA routines and functions and, because these files basically do the same things, they have the same name. These routines exist in their own unique modules each with a different name. Unfortunately, in these files there are differences in the number of columns of information these routines act on so once they have been saved I need them to only act on their own columns only.
My problem is, when I have more than 1 of these files open in the same instance of Excel, I try to run a file using a control letter combination Ctrl-e and it sometimes runs the version of the macro in the wrong file. This leads to all kinds of strange thing happening. What I can do to force the correct macro to work in all instances?
View 2 Replies
View Related
Feb 24, 2011
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
View 4 Replies
View Related
Dec 15, 2006
I have name 28 columns. The range in EACH columns is 6:14000, i.e. the first name is date and the range is from Row B6 to Row B14000.
I am using SumProduct instead of VlookUp to look up values. An example of one of my sumproduct formulas:
SUMPRODUCT(--(Date=$B$6),--(Book=$D$6),PnL___Book)
where the first columns has dates, the next column is bookname and the last column is the profit and loss for each book, i.e. the values that need to be addad.
Now, suddenly I do not get any numbers but just #VALUE! but if I change the range to a lot less, e.g. between Row B6 and B50, it works.
My questions is if this has to do with memory in Excel. Are there limits to how many formulas and how many names you can have before Excel crashes. I am aware that the 28 columns names are very large.
Are there any way of how to get around this, VBA-codes or other formulas that uses less power, not naming columns or what?
View 9 Replies
View Related
Oct 29, 2009
I have an excel sheet that has an onclose macro, and on certain computers i get an error message stating out of memory. can anyone explain why this is and how i could avoid it, by possibly clearing the memory first or something?
View 9 Replies
View Related
Aug 22, 2008
i have a spreadsheet on Excel 2000, which is 256 column's wide and 4000 long the only formulas are countif which start at row 1000-4000 and are copied to column 256. Just for the one sheet it is 14mb is this normal
View 11 Replies
View Related
Mar 31, 2009
I am currently using Excel 2003 to pull from 2 external spreadsheets to compile data. The first sheet (seniority list.xls) being referenced contains all of our employees (approximately 350 rows, but that can vary from 340 to 450) as well as some information (employee ID, full time or part time, etc.). The second sheet being referenced (pay.xls) contains a breakdown of all employees and their pay for a particular bi-weekly period.
What I'm looking to do is:
(1) Find out all of my current employees. I do this by referencing seniority list.xls.
(2) For each employee, find out how many hours they worked each week. I need to do this by referencing pay.xls. Unfortunately, that file works quite differently than seniority list.xls. Unlike the latter, which contains one employee number per row, pay.xls could contain as many or as few rows per employee as their schedule dictates. Each row contains things such as overtime, regular time, double time, alternate regular time, alternate OT, vacation, sick pay, etc. In total, pay.xls could contain as many as approximately 8000 rows. To determine how many hours they work, I'm using this CSE (array) formula:
(Note that the employee ID is in column A of the current sheet.)
View 9 Replies
View Related
Dec 23, 2013
I have a large amount of data on NBA teams and players that is extracted from external web pages. In order to have the data shown on separate sheets to do comparisons I have to use many array formulas and from what I have read that takes up a large amount of memory. I'm now at the point where I'm receiving a message that says "Excel cannot complete this task with available resources" when I open the workbook.
Is there any plausible way that I can resolve this situation? I have thought about converting the lookup formulas from arrays (index/match) to vlookups and hlookups.
I'm not as advanced with using Excel as most of you, and I realize I may be at a point to where I will need to use different software to analyze my data, but I would prefer to stick with Excel as it would be difficult to start from scratch and learn how to use a different program.
View 3 Replies
View Related
Nov 3, 2008
Using Excel 2007, Windows XP, 4gig ram,
I got this "Insufficient Memory .... close some application" message, once in a while.
I know that i got enough memory to run my Excel.
This is a random problem. Memory as been checked with mem86 and it work fine
View 9 Replies
View Related
Oct 4, 2006
Regarding the Memory Leak problem with ADO, does anyone know of a workaround?
The workaround mentioned on Microsoft's website (http://support.microsoft.com/default...;en-us;Q319998) pertains to using ADO to retrieve a recordset from an Excel worksheet, not from an Access Database.
1) Does anyone know if those workarounds would also address retrieving a recordset from an Access Database?
2) If so, is the "SELECT INTO" workaround effective?
Is this Memory Leak issue relatively minor or only affecting Excel 2000? I'm guessing it must be one of those 2 things since the concensus is ADO is superior to MSQuery..
View 2 Replies
View Related
Dec 6, 2006
I'm using a Dell P4, 1.6Ghz, 512Mhz of memory (Win2K & Excel 2003), and it's bogging on spreadsheets that aren't that big and complicated (compared to thing's I did years ago, in the days of 386's and Win98)...
It hits the 100% CPU usage and stays there for minutes (especially when saving) on files that are only around 2M in size, yet it's not using much of the memory...
Spreadsheets large or small seem to have little effect on how much memory is being used... is Win2K allocating a fixed % of total memory for Excel to use?... can this be changed, so Excel gets to use more of the available memory?
I've been cleaning up and redesigning my spreadsheets to make them more efficient, but there seems to be a disconnect between my hardware specs and Excel's performance with files that aren't that big ~:-/
Disabling my antivirus software (Nortons) prior to saving, seems to speed it up a little, but this is not something I should be having to do...
View 9 Replies
View Related
Apr 8, 2007
One of my apps I run is very large and memory intensive, and there are several subsequent procedures which tend to either bog down or errors out before the program finishes running.
Is there a way to clear the memory out?
And adding more physical memory is not an option, I have 4gigs already and Excel can only utilize 1gig anyway.
View 4 Replies
View Related
Jan 27, 2010
I have tried Tools/Options/Security Password to modify, but it can still be saved as a copy to the network which becomes very confusing to many. If I use Tools/Potect Sheet, I need to re-enter the password for all sheets twice. I am trying have it so that everyone is read only, select people can edit (with same password). Password protect all sheets and the workbook with the same password everytime when the workbook is closed
Basically 2 questions
1) Is there a way of remembering a password so that when the workbook is closed it and all sheets are automatically protected with the same password every time
2) Is it possible to have it not copied unless it is opened by an editor
View 2 Replies
View Related
Jan 17, 2014
I have been having an internal debate of how my worksheets shoud be configured.
My workbook could potentially contain 300+ worksheets, 10 control buttons and around 20 macros.
Each worksheet contains data in a range ~ A1:AC40 with many blank cells.
Do you think a workbook with this many worksheets would be unmanageable given the Comp Spec of the user PC below?
Most of the cells are simply short text data.
System info
Processor: 2.3Ghz Dual Processor
RAM: 2GB
32bit
View 7 Replies
View Related
Feb 3, 2010
I have created an excel tool w/ A LOT of macros in it, and the tool "speaks" to the SQL server (downloads data from it and uploads it back). My big problem now is that every time I run any of the VBA code the amount of RAM that is getting allocated to Excel (2007) is increasing - I am checking it in the Task Manager. So if I work for a long time without closing down Excel - my entire RAM is getting consumed and the system slows down, and sometimes crushes.
I was wondering if there is a way in VBA to clean up memory that is used by Excel.
Do you have any hints? I am using extensively 3 things - screenupdating off; displaymessages off, and calculations off. But this speeds up Excel but doesn't reduce the memory usage.
View 7 Replies
View Related
Jun 18, 2014
I have come across one of those annoying 'out-of-memory' errors. See my code below:
Code:
'Dimensions (first rows and cols)
Const frow3 As Integer = 17
Const frow4 As Integer = 19
Const fcol3 As Integer = 10
Const fcol4 As Integer = 11
Sub Extract()
Dim ws As Worksheet, target As Worksheet, meta As Worksheet
[Code] .......
The code executes fine until it gets to End Sub. Some object eats up memory but I don't understand which one.
View 6 Replies
View Related
Jun 19, 2007
How come I keep having the same value for "lastrow". eg: 35
lastrow = ActiveSheet.UsedRange.Rows.Count
It doesn't matter how many rows of data I've changed, it still show lastrow = 35.
I tried to add an extra line above but still doesn't work:
lastrow = 0
lastrow = ActiveSheet.UsedRange.Rows.Count
View 9 Replies
View Related
Jul 11, 2007
two parts:
1.
The following is the VBA version of what i've been using:
Range("A1:J1000").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _
Range("E2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlDescending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
now arrays are bigger than 1000 rows>> even bigger than 65536.
Such an array is stored in "arrBIG" and dimed as (1 to 10, 1 to 100000). The goal is to sort it by the 4'th, then 5th then 6th values in the first dimension - the one that is 1 to 10 - i call this the column.
arrBIG.Sort Key1:=4, Order1:=xlAscending, Key2:= _
5, Order2:=xlAscending, Key3:=6, Order3:=xlDescending _.................
View 9 Replies
View Related
Sep 9, 2008
I'm trying to import data into my sheet with formulae like this in row1(Columns A:I): ='F:Dir[File.xls]Sheet'!A2.
The files I'm importing the data from all have 50.000 rows and 9 columns, so I copy the formulae downward 50.000 times. This generates an error ('Not enough memory'), not surprisingly. I've also tried to do it in steps by way of VBA; first two thousand, copy and paste values, next two thousand etc. But that doesn't work either.
Does anyone know of a solution to this memory problem or a way to work around it (without opening the files with the data in them).
View 9 Replies
View Related
Dec 16, 2008
I have 2 userforms (userform1 and userform2)
userform1 has a combobox1 (list of customers) with an commandbutton1
userform2 has many textboxes
when I click on the userform1.commandbutton1 - userform2 shows with textbox1.text being what was in userform1.combobox1
when i am done with userform2 and click close to go back to userform1, chose a different customer and click the commandbutton1, userform2 opens with the previous value.
how can i clear the previous?
currently when i close userform2 with commandbutton1 i use the following code
Private Sub CommandButton1_Click()
unload userform2
set userform2 = nothing
End Sub
View 9 Replies
View Related
Feb 10, 2009
I have this memory game I want to set out on the computer. In table A, Row 2 (B2:E2) I (Moti) start off being the first player, where I place a series of different items. (In Example is given letters but can be kitchen article food and so…. Separated by coma) these Items are shown to player Jack, for a few seconds, so he can memorize them and Answer in (B3:E3) ....
View 9 Replies
View Related
Jun 3, 2009
I have recently just encountered a problem where whenever I try and run / edit any part of my user-form code i get the following error:
Compile Error:
Out of memory
The help document then lists about 12 things that could cause this.
My user form code is very small, however the form itself is large, it has a multipage with 12 tabs, each tab then contains between 50 and 100 labels which populate with data from the spreadsheet in the background.
Do you think this size could be causing the error? If not, does anyone have any solutions / ideas?
My hardware / software setup is all ok.
View 9 Replies
View Related
May 1, 2006
I 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 Related
May 5, 2006
I have windows XP and xl2k. I have a single- sheet workbook (9MB) linked to another multi-sheet workbook of databases (20MB). The single-sheet workbook has about 100 pictures (30k ea) that, when a number is entered, searches the database. If no match is found for a given picture and product info, then the picture is deleted and the rows are hidden (macros). Usually, about 10 products are found in the database for any one entry.
My goal is that I can "save as > web page" and email these results off to my customer. The problem is the after-the-search result page is about 1MB and so too is the .htm file that is created when I "save as > web page." I was thinking that 10 pictures at 30k should be around 300k plus the formatting (no?). I cleared the "tools>option>general>web options>general>save any additional hidden data..." to little effect. Is there a macro that will delete the hidden rows which I should run AFTER the product search (because deleting them instead of hiding them messes up the macro run) - I'm sure that would help (right?). But my original thought was that the .htm file size would not be affected by hidden rows.
The per-item section of excel is 375 cells + the picture with simple font and formatting and no other objects, etc... When I delete the picture and save as > web page just this one-item section area (375 cells), the .html file is 37kb in size. That seems large to me (yes/no?). (10 x 37K = 370K before pictures!). This may be a reasonable file size, I'm no expert. I read a post where xl2003 has a compression application - is that what I need, or is there a 3rd-party app that can help me reduce the size of this file in htm. Or, is this the best I can expect from this method and I should be exploring elsewhere (metatags?).
View 5 Replies
View Related
Jun 24, 2006
I was reading in the sticky post about the new version of Excel that is due out this year. One of the improvements listed was the increase of usable memory by Excel from 1gig to the amount of Windows memory. In reading this, am I to understand that increasing a machine's physical memory above 1gig is useless for Excel in the current version? My specific issue is I have a very involved worksheet that takes about 2.5 minutes to calculate and this will increase as more data is added, it is heavy formula laden. I was going to increase memory from 2gig to 4gig to assist in reducing the calculation time.
View 3 Replies
View Related
