====== 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>