Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft Excel - Macros - Delete - Delete unused Rows and Cols ======

Delete all unused rows and columns in the sheet.

<code excel>
' Delete all unused rows and columns in the sheet.
' Note: This code may not work correctly if the worksheet contains merged cells.
Sub DeleteUnusedOnSheet(ws As String)
 
    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim wks As Worksheet

    With Worksheets(ws)
        myLastRow = 0
        myLastCol = 0
        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

End Sub
</code>