Delete all unused rows and columns in the sheet.

' 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