Delete Unused Cells

[DaveRadford]

Afternoon,

I have a piece of code that will shrink down the size of my spreadsheet from 12mb to 3mb. This code works but it will not run when worksheets are protected.

Seems to point to:

Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete


I have tried placing the following in various points of the code but still fails. I am doing this upon save so it will tidy it up hence why it needs to go back to being protected once its done.

ActiveSheet.Unprotect Password:="password"

and

ActiveSheet.protect Password:="password"



Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets

With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
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

Next wks

 

[DaveRadford]

that no longer errors however it does not shrink the excel document to the size it used to remains original size as without previous code.