Select the last cell that contains data in the worksheet
To delete any unused rows:
Move down one row from the last cell with data.
Hold the Ctrl and Shift keys, and press the Down Arrow key
Right-click in the selected cells, and, from the shortcut menu, choose Delete
Select Entire Row, click OK.
To delete any unused columns:
Move right one column from the last cell with data.
Hold the Ctrl and Shift keys, and press the Right Arrow key
Right-click in the selected cells, and, from the shortcut menu, choose Delete
Select Entire Column, click OK.
Save the file. Note: In older versions of Excel, you may have to Save, then close
and re-open the file before the used range is reset.
To programatically reset the used range,
Note: This code may not work correctly if the worksheet contains merged cells. To
check your worksheet, you can run the TestForMergedCells code.
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
'================================
Sub TestForMergedCells()
Dim AnyMerged As Variant
AnyMerged = ActiveSheet.UsedRange.MergeCells
If AnyMerged = False Then
MsgBox "no merged"
ElseIf AnyMerged = True Then
MsgBox "all merged"
ElseIf IsNull(AnyMerged) Then
MsgBox "mixture"
Else
MsgBox "never gets here--only 3 options"
End If
End Sub