> Could be the used range Excel estimates on a sheet.
> Sometimes gets it wrong.
[quoted text clipped - 4 lines]
> NOW the IMPORTANT step........save the workbook.
> Sometimes save, close and re-open to see changes.
Bob
This "inflation" thing is man made. What Excel "thinks" is the extent
of the used range is a big driver in the size of your file. For example, do
this:
Open a new blank file.
Enter something in A1.
Then enter something in C5.
Then enter something in G10.
Now do Ctrl - End.
Excel jumps to G10. Excel thinks G10 is the last cell in the used range.
Excel is right.
Now delete the entry in G10.
You know that the last cell of "your" used range is now C5.
Do Ctrl - End.
Excel jumps to G10 and will always jump to G10 regardless of how many
entries before G10 you delete.
When you do this with hundreds of columns and thousands or rows and dozens
of sheets, the significance of Excel "thinking" what the used range is can
be staggering.
Yes, there is a VBA way of doing what Gord said to do. The macro is below.
HTH Otto
This code is from Debra Dalgliesh at:
http://www.contextures.on.ca/xlfaqApp.html#Unused
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
>> Could be the used range Excel estimates on a sheet.
>> Sometimes gets it wrong.
[quoted text clipped - 11 lines]
> How does this inflation happen and how can it be prevented? Could the
> shrinking operation be automated with a macro I wonder?
Jim Moberg - 13 Oct 2006 14:20 GMT
Thanks for the info. I have never put vba code into an excel sheet before.
Do you just insert it into the sheet itself or is there some other place you
enter it?
> Bob
> This "inflation" thing is man made. What Excel "thinks" is the extent
[quoted text clipped - 114 lines]
> > How does this inflation happen and how can it be prevented? Could the
> > shrinking operation be automated with a macro I wonder?
Excel is a disease and it shouldn't be used for data entry
use the right tool for the job and lose the training wheels!
> > Could be the used range Excel estimates on a sheet.
> > Sometimes gets it wrong.
[quoted text clipped - 11 lines]
> How does this inflation happen and how can it be prevented? Could the
> shrinking operation be automated with a macro I wonder?