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

 

[doofusboy]

Have you tried:

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
[B][COLOR=red].Unprotect Password:="password"
[/COLOR][/B].Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
[B][COLOR=red].Protect Password:="password"
[/COLOR][/B]End If

End With

Next wks

 

[Norie]

Have you tried using wks instead of ActiveSheet when you are unprotecting the worksheet(s)?

 

[DaveRadford]

Hello again,

I have used the code above, and it works...ish. The isue is i have 56 worksheets and it systematically goes through each and everyone tidying them up.

Is there a quicker way as saving an take up to 3 minutes a time now, but my main concern.....as it checks each worksheet it actually changes to the view so you are shown each worksheet it is checking making it obvious what its doing.

Is there a way to do it where the worksheet that is displayed stays put?

Thanks,

Dave

 

[Kaps_mr2]

I think you mean :-

Application.ScreenUpdating = False

which will turn off screen updates, and then:-

Application.ScreenUpdating = True

Thanks

Kaps

 

[Jon von der Heyden]

Is there a way to do it where the worksheet that is displayed stays put?

You can use:
Application.ScreenUpdating = False
at the start of the routine, and at the end:
Application.ScreenUpdating = True

This is typical good practise to make code more efficient.

Are you aware that the UsedRange property can also be used as a method to reset the size of the used range? I.e., simply putting:

Sheets(1).UsedRange

..will reset the used range.

 

[DaveRadford]

No i wasnt aware of that, interesting. As you can see from an earlier reply i had it looks to be a serious amount of code in comparison.

If i wanted it to reset the used range on each sheet, could i do soemthing like worbook.usedrange ?

or would it just be easier to do soemthing like:

worksheet(1).usedrange
worksheet(2).usedrange
worksheet(3).usedrange

etc

keeping in mind efficient code i doubt it

 

[Jon von der Heyden]

You're on the right track, but it depends. You can look for the last occupied cell in a sheet and then delete all subsequent columns and rows. That method will also therefore delete rows/columns where they still contain formatting (e.g. fill color) - since your find code doesn't look for the last formatted cell, but rather the last cell that contains a value.

UsedRange will still consider anything that is used, formatting and all.

 

[DaveRadford]

I was testing this and when i tried to use:

Worksheet(2).UsedRange
Worksheet(3).UsedRange
Worksheet(4).UsedRange
Worksheet(5).UsedRange

(3) = the worksheet number i also tried Worksheet("Name").UsedRange.

Both cam up with sub or Function not defined, im using it in a macro which im calling in the worksheet. Should this be in the spreadsheet?

 

[Jon von der Heyden]

Try;
Worksheets(1).UsedRange