ResetUsedRange after a Print-preview causing wierd errors.

[John Keith]

What can cause this code to fail with the "Delete method of Range Class
Failed?"

I found this ResetUsedRange code posted on a previous message. It had been
working with out a glitch for several months. When I made a simple change to
swap the formatted print out from xlPortrait to xlLandscape. I can run the
macros that pull data from a SQL database over and over with out a problem...
but 1 time of doing a print-preview to see that the report is now in
landscape; close out; then run this code again to try and pull data... it
fails. this has been confirmed three times in a row. The code causes every
cell to enlarge to 200x200 pixels and the fonts to be about 24point size and
all screenupdating off, scroll bars are overlayed with portions of fonts that
are too big to fit in the cell. Scrolling (with the mouse wheel) causes
character ghosts all over the screen. No graceful exit is allowed. Either
Excel will crash out or I have to Task-list close. Then the file can be
opened and all works fine untill a print-preview is done again.. then I have
to kill it again.

Public Sub ResetUsedRange()
Dim iLastRow As Long
Dim iLastCol As Long
Dim rng As Range

With ActiveSheet
iLastRow = 0
iLastCol = 0
Set rng = .UsedRange
On Error Resume Next
iLastRow = Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iLastCol = Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
On Error GoTo 0
.Range(.Cells(iLastRow + 1, 1), _ '********* ERROR HERE
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, iLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End With
End Sub

What does print-preview do that makes this code fail?

*********************************************************
After the weird screen comes up (as described above) this code then fails
with clear method of range class failed.

WBName = ActiveWorkbook.Name
ctlUploadWinSNAP.Enabled = False
If Worksheets("WinSNAP-POs").AutoFilterMode Then Selection.AutoFilter
ActiveWindow.FreezePanes = False
Workbooks(WBName).Worksheets("WinSNAP-POs").UsedRange.Select
Selection.Clear 'ERROR HERE

I can't figure out what condition the worksheet is left in after that error.
In a new workbook I can't make a usedrange selection that will cause the
error. Blank or filled sheet works fine either way. (I am going to keep
playing with this though and see if I can recreate the error)

I think I can make it all work 100% of the time if print-preview was
disabled.

 

[John Keith]

Just doing a Print causes the error too.

After the print the dashed lines to show page breaks are visible. All looks
ok after the print. If I exit (save or not) then reopen, the macros work
fine. Its only when I try to run any of the stored macros after a
print/preview, with in the same instance, has been done that excel become
unstable.

 

[Martin]

It's a few months on but I've had similar problems/messages to you, not with
printing/previewing but simply from a line of code that switches
Application.ScreenUpdating on again. I guess it's a "memory issue".