Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Delete Sheet Problem, Excel Crash !

  Asked By: Navin    Date: Jan 05    Category: MS Office    Views: 4099
  

I am using this sub to delete expired sheets within a worksheet by
comparing the date of sheet name with the current date. However,
running this sub always results in Excel crash ! What I mean, the
Excel is frozen and I am asked to quit the Excel completely. I cannot
even go into the VBA again to check the line.

Sub DeleteOld()
For Each Sheet In Worksheets

If IsDate(Sheet.Name) Then
If DateValue(Sheet.Name) <= DateValue(Date - 14) Then
Application.DisplayAlerts = False
Sheet.Delete
End If

End If
Next

End Sub

I have to reopen the file again, but the crash always happens when I
run the Sub again.

So, I tried to change Sheet.Delete to something like
Sheet.Cells.Delete or Sheets.Cells.Clearcontents, the VBA can run
without problem after change. But once I restored to Sheet.Delete,
again the crash problem came out.

I used this kind of Sub in the other Excel file, the problem still
persists.

Could somebody tell me why ?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Fairuzah Alam     Answered On: Jan 05

I think that the problem  is that when it sets up the for each sheet  it
identifies that there are, say 7 sheets. When it deletes one it does not
alter this number. so it eventually gets to a non-existent sheet.

Try doing it backwards

something like (untested code)

for x = worksheets.count to 1 step -1
With worksheets(x)
If IsDate(.Name) Then
If DateValue(.Name) <= DateValue(Date - 14) Then
Application.DisplayAlerts = False
.Delete
End If

End If


Next x

 
Answer #2    Answered By: Gerardo Morgan     Answered On: Jan 05

I can track down the problem  caused by
extensive recalculation whenever a sheet  is deleted.

Therefore, I have to turn off the automatic recalculation and turn it
on again  at the end.

Sub DeleteOld()

Application.Calculation = xlCalculationManual

For Each Sheet In Worksheets

If IsDate(Sheet.Name) Then
If DateValue(Sheet.Name) <= DateValue(Date - 14) Then
Application.DisplayAlerts = False
Sheet.Delete
End If

End If
Next

Application.Calculation = xlCalculationAutomatic

End Sub

 
Answer #3    Answered By: Kawakib Mansour     Answered On: Jan 05

No. It is not excessive calculation but trying to access a sheet  that does
not exist. To avoid this you have to process the sheets  from back to front
rather than from front to back

 
Didn't find what you were looking for? Find more on Delete Sheet Problem, Excel Crash ! Or get search suggestion and latest updates.




Tagged: