Worksheet size reduction

F

Frank Stone

I'm not entire sure what you mean but try this:
macro for clearing unused space when scroll bars don't
work right.
Sub DeleteUnused()

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("*", 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
 
Top