Clean up formatting.

A major cause of Spreadsheet bloat.

Also see John Walkenbach's macro to reset the last cell:

Automatically Resetting the Last Cell
Paste the following code in a module using the Visual Basic Editor (Alt+F11, Insert>Module)


Sub ClearExcessRowsAndColumns()

'Richard Curzon

Dim intLRow As Integer, intLCol As Integer, intRowCount As Integer

Dim i As Integer, intLCell As Integer, wksWKS As Worksheet

Dim blProtCont As Boolean, blProtScen As Boolean, blProtDO As Boolean

On Error GoTo errHandler

'Loop through each worksheet in the workbook.

For Each wksWKS In ActiveWorkbook.Worksheets

Application.StatusBar = "Finding Data Boundaries for " & _
ActiveWorkbook.Name _
& "!" & wksWKS.Name & ", Please Wait..."

'Store worksheet protection settings and unprotect if protected.

blProtCont = wksWKS.ProtectContents

blProtDO = wksWKS.ProtectDrawingObjects

blProtScen = wksWKS.ProtectScenarios

wksWKS.Unprotect ""

'Determine the last cell that Excel finds and determine its row.
'If it is greater than 8192, limit the rows for variable memory

If wksWKS.Cells.SpecialCells(xlLastCell).Row > 8192 Then

'Last row for Lotus files is 8192.

intRowCount = 8192


intRowCount = wksWKS.Cells.SpecialCells(xlLastCell).Row

End If

'Loop through each row and determine the last cell with data.

intLCell = 0

intLCol = 0

For i = 1 To intRowCount

intLCell = wksWKS.Cells(i, 255).End(xlToLeft).Column

If intLCell > intLCol Then intLCol = intLCell

Next i

'Loop through the columns and determine the last cell with data.

intLCell = 0

intLRow = 0

For i = 1 To wksWKS.Cells.SpecialCells(xlLastCell).Column

intLCell = wksWKS.Cells(8194, i).End(xlUp).Row

If intLCell > intLRow Then intLRow = intLCell

Next i

'Delete the Excess rows and columns.

wksWKS.Range(wksWKS.Columns(intLCol + 1), _

wksWKS.Range(wksWKS.Rows(intLRow + 1), _

'Reset protection.

wksWKS.Protect "", blProtDO, blProtCont, blProtScen

Next wksWKS

Application.StatusBar = False

Exit Sub


MsgBox "An error occurred." & Chr(13) & Err.Number & " " & _
Err.Description, vbCritical

End Sub

Also see:

Richard Cruzon

Valid CSS! 
Last edited 11 January, 2008
Links verified 11 January, 2008