Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:macros:delete_unused_rows_and_cols [2021/08/04 14:20] – created peter
+++ microsoft_excel:macros:delete_unused_rows_and_cols [2021/08/04 15:01] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== Microsoft Excel - Macros - 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>