Programming Forums > Application Development > Visual Basic

crmpicco · May 10th, 2005, 4:08 AM

I'm after code that can
Delete all empty worksheets.

I have this, but it doesnt work.

I get method 'delete' of object 'worksheet' failed.

(Toggle Plain Text)

Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
      If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then
        Application.DisplayAlerts = False
        sh.Delete
        Application.DisplayAlerts = True
      End If
    Next

TIA.

Picco

Rory · May 12th, 2005, 12:39 PM

It works for me, it just raises an error when you get to the last sheet (you need at least one in a workbook!): normally when you get a method failed error in VBA it means something else has a lock on the object: where do you run this code?

May 10th, 2005, 4:08 AM	#1
crmpicco Newbie Join Date: Feb 2005 Posts: 13 Rep Power: 0	Delete all empty worksheets: EXCEL VBA I'm after code that can Delete all empty worksheets. I have this, but it doesnt work. I get method 'delete' of object 'worksheet' failed. (Toggle Plain Text) Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True End If Next Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True End If Next TIA. Picco

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 12th, 2005, 12:39 PM	#2
Rory Expert Programmer Join Date: Jan 2005 Location: London Posts: 542 Rep Power: 4	It works for me, it just raises an error when you get to the last sheet (you need at least one in a workbook!): normally when you get a method failed error in VBA it means something else has a lock on the object: where do you run this code?

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Visual Basic

Delete all empty worksheets: EXCEL VBA