Delete Unused

[DaveRadford]

Afternoon,

I have a sub in a macro that will shrink the size of my excel workbook as when used the temp data of having cells full of data and then not causes the spreadsheet to grow large.

The code is:

Sub DeleteUnused()

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
End Sub

Now i have a couple issues:

If i put the code anywehre else apart from at the end it fails with error to do with:

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


If i have it at the end it also prompts "Do you want to save the changes" I dont want this to appear as i have a save& exit button itdefeats the point.

Full Save&Exit Code:

Sub SaveandExit_Click()
Dim Answer As String
Dim MyNote As String
MyNote = "Save Changes & Exit?"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Save Changes & Exit")
If Answer = vbNo Then

Application.DisplayFullScreen = True

End If

If Answer = vbYes Then

ThisWorkbook.Save
Application.Quit
Application.DisplayFullScreen = False

DeleteUnused

End If


End Sub

Thanks,

 

[Jon von der Heyden]

I haven't read your entire code (I find it difficult when not placed in code tags and not indented), but I think you are looking to reset the used range. If that is the case all you need is:

For Each wks in ThisWorkbook.Worksheets
    wks.UsedRange
Next wks

 

[DaveRadford]

Thanks for the reply, ill remember formatting in future, is that code in place of all my original code? or is it to be inserted somewere within it?

Thanks,

 

[Jon von der Heyden]

Replace the code within your DeleteUnused sub-routine with this code, but keep your wks variable declaration.

 

[DaveRadford]

The sub i have in my module which ireerence in the sheet is now:

Sub DeleteUnused()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
wks.UsedRange
Next wks

End Sub

I get the error:

"Invalid use of property error"

and then the spreadsheet crashes.

Thanks,

 

[Jon von der Heyden]

My bad. I get the same error, but strange that it crashes

Try;

[COLOR="Blue"]Sub[/COLOR] DeleteUnused()
    [COLOR="Blue"]Dim[/COLOR] wks [COLOR="Blue"]As[/COLOR] Worksheet
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] wks [COLOR="Blue"]In[/COLOR] ThisWorkbook.Worksheets
        Sheets(wks.Index).UsedRange
    [COLOR="Blue"]Next[/COLOR] wks
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[DaveRadford]

The ode no longer errors, although the orginal code reduced the spreadsheet from 12-13mb to what it shoul be 3-4mb. Unfounately the code you asked me to try doesnt seem to delete teh unused range and the spreadsheet satys at 12mb. I'm using the code in this button click sub.

Sub SaveandExit_Click()

Dim Answer As String
Dim MyNote As String

DeleteUnused

MyNote = "Save Changes & Exit?"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Save Changes & Exit")
If Answer = vbNo Then

Application.DisplayFullScreen = True

End If

If Answer = vbYes Then

ThisWorkbook.Save
Application.Quit
Application.DisplayFullScreen = False



End If


End Sub


​

 

[DaveRadford]

Alternatively:

do you have any code that would get rid of/default to yes the following message:

If i have it at the end it also prompts "Do you want to save the changes" I dont want this to appear as i have a save& exit button itdefeats the point.

preferably ur code looks nicer and im hoping wont mess up the scope of my defined name as the old one used to