Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Out of memory. Why?
I am creating a new spreadsheet from data from another spreadsheet. It is
basically a subtotal spreadsheet but the process of subtotalling is much more complex because some of the data can be subtotalled and other columns are just info. So I'm using v-lookup and various other formulas to copy the data over. The problem is that I've hit a point where Excel has told me: Excel cannot complete this task with available resources. Choose less data or close other applications. I have to say that I don't think that I should be getting this message because even though some of the stuff that I'm doing is complex there isn't much data there and I'm always getting rid of the formulas by copying and pasting the values over the formulas. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Out of memory. Why?
Carl
How big is your file? The most common cause of a file "growing" out of proportion is where Excel thinks the last used cell is vastly different that what it really is. Do this: In each sheet, scroll to the last cell (last row and last column). Note the cell address. Then do Ctrl-End. Excel shows you what it thinks is the last used cell. If those 2 cells are vastly displaced from each other, that might be your problem. Note that the size of the file is predicated on, among other things, the size of the used range. Post back with your findings. HTH Otto "carl" wrote in message ... I am creating a new spreadsheet from data from another spreadsheet. It is basically a subtotal spreadsheet but the process of subtotalling is much more complex because some of the data can be subtotalled and other columns are just info. So I'm using v-lookup and various other formulas to copy the data over. The problem is that I've hit a point where Excel has told me: Excel cannot complete this task with available resources. Choose less data or close other applications. I have to say that I don't think that I should be getting this message because even though some of the stuff that I'm doing is complex there isn't much data there and I'm always getting rid of the formulas by copying and pasting the values over the formulas. Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Out of memory. Why?
Hi Otto,
I think you've managed to kill two birds with one stone here. I've always wondered why the range size is increased in certain sheets when it's obvious that the range of my data is far smaller. In the workbook that I'm having problems with there are 30 sheets and quite a few of them have a range of roughly M65000. This is obviously one of the problems. Can you please tell me how to get the range back down to the correct size? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Out of memory. Why?
Carl
There are 2 ways to do this. Both do the same thing but one is manual and the other is with VBA. First the manual. You would do this with each sheet. Say your used range's last cell (actual, not Excel's) is K100. Click on row number 101. Click on the row number, not some cell in that row. Do Ctrl-Shift-down arrow. This selects every row from 101 down. Click Edit - Delete. Click on the column letter of Column L. Do Ctrl-Shift-Right arrow. This selects every column from L to the far right. Click Edit - Delete. Save the file. You may need to close and reopen the file. Done. The VBA way does all that for you for every sheet, all in one swoop. Run the following macro. HTH Otto Sub ResetUsedRange() Dim myLastRow As Long Dim myLastCol As Long Dim wks As Worksheet Dim dummyRng As Range Dim s As Shape For Each wks In ActiveWorkbook.Worksheets With wks On Error Resume Next For Each s In ActiveSheet.Shapes s.Placement = xlMoveAndSize Next s On Error GoTo 0 myLastRow = 0 myLastCol = 0 Set dummyRng = .UsedRange On Error Resume Next myLastRow = _ .Cells.Find("*", After:=.Cells(1), _ LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchDirection:=xlPrevious, _ searchorder:=xlByRows).Row myLastCol = _ .Cells.Find("*", After:=.Cells(1), _ LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchDirection:=xlPrevious, _ searchorder:=xlByColumns).Column On Error GoTo 0 If myLastRow * myLastCol = 0 Then .Columns.Delete Else .Range(.Cells(myLastRow + 1, 1), _ .Cells(.Rows.Count, 1)).EntireRow.Delete .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete End If End With Next wks End Sub "carl" wrote in message ... Hi Otto, I think you've managed to kill two birds with one stone here. I've always wondered why the range size is increased in certain sheets when it's obvious that the range of my data is far smaller. In the workbook that I'm having problems with there are 30 sheets and quite a few of them have a range of roughly M65000. This is obviously one of the problems. Can you please tell me how to get the range back down to the correct size? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Out of memory. Why?
If your sheet has some merged cells, things can act strangly when trying to
change the .UsedRange via code. Contextures has code to do it and to handle sheets with merged cells: http://www.contextures.com/xlfaqApp.html#Unused "carl" wrote: Hi Otto, I think you've managed to kill two birds with one stone here. I've always wondered why the range size is increased in certain sheets when it's obvious that the range of my data is far smaller. In the workbook that I'm having problems with there are 30 sheets and quite a few of them have a range of roughly M65000. This is obviously one of the problems. Can you please tell me how to get the range back down to the correct size? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 memory issue | Excel Discussion (Misc queries) | |||
Out of memory? | Excel Discussion (Misc queries) | |||
Out of Memory - Display err msgs | Setting up and Configuration of Excel | |||
Memory Leak in Excel | Excel Discussion (Misc queries) | |||
Worksheet Memory hog | Excel Discussion (Misc queries) |