A client gave me a workbook that according to them was 28MB but has recetly
gone up to 103MB, without any major changes. I noticed that it is currently
shared.

I told them I'd try and get it back down to a more reasonable file size. On
each of the 60 sheets I had them in Column A - the last active Row enter
"LR" (without the quotes ALSO in ROW 1:1 - the Last active Column enter "LC".

I removed the Share; I set the Calculation Method to Manual and I ran this
following Macro: But File is still 100++ MBs.. Any thoughts??

Sub RidBloat()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("LR", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("LC", 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

There a a couple of sheets that AFTERWARDS when I do a Control+End on, it
seems the Macro is not reading the LR meaning the LR in in Cell A90, but
the
Control+End takes me to cell AB400 (AB1 = LC). I manually selected the Row
headers from A91 to A65536 and deleted the Rows. then back to Control + End
and still I'm taken to cell AB400; What gives??

"Jim May" wrote:

A client gave me a workbook that according to them was 28MB but has recetly
gone up to 103MB, without any major changes. I noticed that it is currently
shared.

I told them I'd try and get it back down to a more reasonable file size. On
each of the 60 sheets I had them in Column A - the last active Row enter
"LR" (without the quotes ALSO in ROW 1:1 - the Last active Column enter "LC".

I removed the Share; I set the Calculation Method to Manual and I ran this
following Macro: But File is still 100++ MBs.. Any thoughts??

Sub RidBloat()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("LR", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("LC", 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

Jim

After deleting unused rows and columns you will have to SAVE the workbook before
CTRL + End will go to real used range.


Gord Dibben MS Excel MVP

On Sun, 11 Nov 2007 14:29:00 -0800, Jim May
wrote:

There a a couple of sheets that AFTERWARDS when I do a Control+End on, it
seems the Macro is not reading the LR meaning the LR in in Cell A90, but
the
Control+End takes me to cell AB400 (AB1 = LC). I manually selected the Row
headers from A91 to A65536 and deleted the Rows. then back to Control + End
and still I'm taken to cell AB400; What gives??

"Jim May" wrote:

A client gave me a workbook that according to them was 28MB but has recetly
gone up to 103MB, without any major changes. I noticed that it is currently
shared.

I told them I'd try and get it back down to a more reasonable file size. On
each of the 60 sheets I had them in Column A - the last active Row enter
"LR" (without the quotes ALSO in ROW 1:1 - the Last active Column enter "LC".

I removed the Share; I set the Calculation Method to Manual and I ran this
following Macro: But File is still 100++ MBs.. Any thoughts??

Sub RidBloat()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("LR", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("LC", 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

Gord, Guess I should add the line beforethe End Sub

Thisworkbook.save

Thanks,
Jim

"Gord Dibben" wrote:

Jim

After deleting unused rows and columns you will have to SAVE the workbook before
CTRL + End will go to real used range.


Gord Dibben MS Excel MVP

On Sun, 11 Nov 2007 14:29:00 -0800, Jim May
wrote:

There a a couple of sheets that AFTERWARDS when I do a Control+End on, it
seems the Macro is not reading the LR meaning the LR in in Cell A90, but
the
Control+End takes me to cell AB400 (AB1 = LC). I manually selected the Row
headers from A91 to A65536 and deleted the Rows. then back to Control + End
and still I'm taken to cell AB400; What gives??

"Jim May" wrote:

A client gave me a workbook that according to them was 28MB but has recetly
gone up to 103MB, without any major changes. I noticed that it is currently
shared.

I told them I'd try and get it back down to a more reasonable file size. On
each of the 60 sheets I had them in Column A - the last active Row enter
"LR" (without the quotes ALSO in ROW 1:1 - the Last active Column enter "LC".

I removed the Share; I set the Calculation Method to Manual and I ran this
following Macro: But File is still 100++ MBs.. Any thoughts??

Sub RidBloat()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("LR", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("LC", 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