Delete Unused

DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63
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,
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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:

Code:
For Each wks in ThisWorkbook.Worksheets
    wks.UsedRange
Next wks
 
Upvote 0
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,
 
Upvote 0
Replace the code within your DeleteUnused sub-routine with this code, but keep your wks variable declaration.
 
Upvote 0
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,
 
Upvote 0
My bad. I get the same error, but strange that it crashes :eeek:

Try;
Code:
[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]
 
Upvote 0
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


 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,368
Messages
6,171,682
Members
452,416
Latest member
johnog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top