Page 178 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Delete Method
The Delete method is complementary to the Add method; rather than putting additional
sheets in your workbook, you can delete any sheet from a workbook. When you use the
Delete method from the Sheets collection, you can delete any sheet in the workbook, but if
Chapter 7
you use the Delete method from the Worksheets collection, you’ll be assured of not deleting a
chart sheet by accident (and the same is true if you try to delete a worksheet using the Charts
collection’s Delete method).
The syntax for deleting a sheet follows the familiar pattern of naming the collection from
which you want to delete the sheet, naming the sheet using either the sheet’s name or its posi-
tion in the collection, and then invoking the Delete method. As an example, any of the follow-
ing lines of code would delete the worksheet named Summary, provided it was the fourth
sheet of a workbook:

Worksheets("Summary").Delete
Sheets("Summary").Delete
Worksheets(4).Delete
Sheets(4).Delete
You could also delete the active sheet using the ActiveSheet property, as in this statement:

ActiveSheet.Delete
One thing that’s important to notice, however, is that the last of the four lines of code listed
in the preceding example wouldn’t delete the correct sheet if there were a chart sheet any-
where among the first four sheets of the workbook. As an illustration, consider a workbook
created for The Garden Company with five sheets, the fourth of which is a chart sheet.
In this workbook, the fourth member of the Worksheets collection is the Summary worksheet,
but the fourth member of the Sheets collection is the Q1Sales chart sheet.

Tip Delete by Name, Not Position
When possible, you should always refer to sheets by name to ensure you delete the correct
ones. The exception to that guideline would be when you’re deleting all but one sheet in a
workbook and that sheet is in a known position or it has a known name (for example, you
moved it to the front or the back of the workbook or gave it a specific name that’s hard-
coded into your procedure). If that’s the case, you can use a For Each…Next loop to delete
all but the first or last sheet, or to skip over a sheet with a specific name. Be sure to test
your code on dummy workbooks before putting it to work on real data, though.

When you invoke the Delete method, Excel displays an alert box asking if you’re sure you
want to delete the worksheet. Of course, the last thing you want to see when you’re using an
automated procedure is an alert box that requires human intervention for the procedure to
continue. You can use the Application object’s DisplayAlerts property to turn off alert boxes,
however, so you can keep the expected alert box from appearing during a known operation.
It’s usually a good idea to turn alerts back on, though, so that if something unexpected happens,
your procedure won’t proceed without you or a colleague ensuring no harm will be done.

152