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
'overload.

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

'Last row for Lotus files is 8192.

intRowCount = 8192

Else

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.Columns(255)).Delete

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

'Reset protection.

wksWKS.Protect "", blProtDO, blProtCont, blProtScen

Next wksWKS

Application.StatusBar = False



Exit Sub



errHandler:

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

End Sub

Also see:

Richard Cruzon

Last edited 11 January, 2008

Links verified 11 January, 2008