Out of memory. Why?

[carl]

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?

 

[Otto Moehrbach]

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]

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?

 

[Otto Moehrbach]

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

 

[JLatham]

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