Reset Used Range, Debra Dalgliesh's code

[Otto Moehrbach]

Excel 2002, WinXP
I ran Debra Dalgliesh's code to reset the used range on my file of some 12
sheets. I include her code below for reference.

The code hung up on several of the sheets (not all) with the error "Cannot
shift objects off sheet." The offending line of code was the command to
delete the columns, near the end of the macro.

I tracked down the individual sheets and the last row and last column. I
tried to manually delete the columns and got the same error.

I then selected the columns to delete and did (manually) Edit - Clear - All.

I then ran the code again and all went well with that sheet.

When the code hung up on another sheet, I inserted code to clear the range
before deleting the columns. I used the "Clear" command because the macro
recorder recorded "Clear" when I manually did Edit - Clear - All.

That didn 't work for me. The code still hung up on the same sheet. I had
to manually do Edit - Clear - All with each offending sheet over the range
of columns to be deleted before the code would reset the used range.

My "Clear - All" line of code and Debra's column-delete line of code is as
follows:
..Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).Clear
..Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete

Question: How can I code to clear everything, objects and all, in a range?
Thanks for your help. Otto

Sub ResetUsedRange()
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

 

[Tom Ogilvy]

Any help in this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;211769&Product=xlw
"Cannot shift objects off sheet" error message when you hide columns in
Excel

 

[Otto Moehrbach]

Thanks Tom. I took the little macro code that the article gave to change
the placement property of all the objects on the sheet, if any, and put the
code into Debra's macro. Otto