First, I pasted too much:

call Sub DeleteUnused
should be
call DeleteUnused

====
And I've used that code lots of times without error.

But remember that since it's deleting rows, it can only work against an
unprotected worksheet.

Any chance that your sheet is protected?

This minor tweak will stop the error if a worksheet is protected.

Option Explicit
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
If wks.ProtectContents = True _
Or wks.ProtectDrawingObjects = True _
Or wks.ProtectScenarios = True Then
'do nothing
Else
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 If
End With
Next wks

End Sub


TMiGNa wrote:

I have the code installed, but when I try to run it, I get the message
"run-time error '1004': Application-defined or object-defined error" and the
debugger highlights this part of the code. Do I need to add or remove
something? Thanks for your patience with me.

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

"Dave Peterson" wrote:

The subroutine "Sub DeleteUnused()" on Debra's site actually tries to reset the
last used cell of all the worksheets in the active workbook.

So you could make that just another procedure in your project (at the bottom of
the module -- or in a whole different module).

Then just add:

call Sub DeleteUnused

to your existing code (as long as the workbook to "clean" is active).

So you can do it beforehand -- right at the start???.

Or do it right after you copy to the new workbook and just clean up that
workbook with a single sheet.

=======
Personally???

I have this same kind of macro in my personal.xl* file. I have it assigned to a
short cut key (ctrl-t isn't used by excel, so I use that).

I find it useful for lots and lots of workbooks and just run it when I want. In
my case, I'd run my personal.xl* version (via the shortcut key), then run your
existing code.

If I have to share with others, I'd embed it into the routine (with "call Sub
DeleteUnused").




TMiGNa wrote:

Ah - see it. So here's where my newbie questions come in, so please be
patient with me.... would a plug that code in as a new macro, or add it to
the already existing macro? If the later, then how should it be added to that
code? My guess is as a new macro, but can a document utilise multiple macros?
Please forgive my lack of knowledge. Thanks!

"Dave Peterson" wrote:

And there was code in that link to Debra's site that could be used to reset the
last used cell.



--

Dave Peterson


--

Dave Peterson