Hi Rick,
Again nice one. But I do not seem any practical gain over this function. The major issue is workbook size. Formatting the cells sometimes dramatically increases the workbook size. So I would go with the following procedure which reduces the size of the workbook by deleting all those unwanted formatted cells.
Run the code on each sheet, save and close the workbook, reopen. The usedrange will reset with the actual data.
Code:Option Explicit Sub CleanWorksheet(Optional ByRef WkSht As Worksheet) Dim LastRow As Long Dim LastCol As Long If WkSht Is Nothing Then Set WkSht = ActiveSheet With WkSht LastRow = .Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious).Row LastCol = .Cells.Find(what:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column .Range(.Cells(1, LastCol + 1), .Cells(1, .Columns.Count)).EntireColumn.Delete .Range(.Cells(LastRow + 1, 1), .Cells(.Rows.Count, 1)).EntireRow.Delete End With End Sub




Reply With Quote
Bookmarks