Delete Method

Applies To

Arc Object, Arcs Collection, Axis Object, AxisTitle Object, Button Object, Buttons Collection, Characters Object, Chart Object, ChartObject Object, ChartObjects Collection, Charts Collection, ChartTitle Object, CheckBox Object, DataLabel Object, DataLabels Collection, DialogSheet Object, DialogSheets Collection, DownBars Object, Drawing Object, DrawingObjects Collection, Drawings Collection, DropDown Object, DropDowns Collection, DropLines Object, EditBox Object, EditBoxes Collection, ErrorBars Object, Gridlines Object, GroupBox Object, GroupBoxes Collection, GroupObject Object, GroupObjects Collection, HiLoLines Object, Label Object, Labels Collection, Legend Object, LegendEntry Object, LegendKey Object, Line Object, Lines Collection, ListBox Object, ListBoxes Collection, Menu Object, MenuBar Object, MenuItem Object, Module Object, Modules Collection, Name Object, OLEObject Object, OLEObjects Collection, OptionButton Object, OptionButtons Collection, Oval Object, Ovals Collection, Picture Object, Pictures Collection, Point Object, Range Object, Rectangle Object, Rectangles Collection, Scenario Object, ScrollBar Object, ScrollBars Collection, Series Object, SeriesLines Object, Sheets Collection, SoundNote Object, Spinner Object, Spinners Collection, Style Object, TextBox Object, TextBoxes Collection, TickLabels Object, Toolbar Object, ToolbarButton Object, Trendline Object, UpBars Object, Worksheet Object, Worksheets Collection.

Description

Deletes the object. Syntax 2 applies only to Range objects.

Syntax 1

object.Delete

Syntax 2

object.Delete(shift)

object

Required. The object to which this method applies. Syntax 2 applies only to Range objects.

shift

Optional. Specifies how to shift cells to replace the deleted cells (either xlToLeft, or xlUp). If this argument is omitted, Microsoft Excel selects a default based on the shape of the range.

Remarks

Attempts to delete a built-in Toolbar or MenuBar object will fail, but will not cause an error. This allows you to use a For Each loop to delete all custom tool bars or menu bars.

Deleting a Point or LegendKey deletes the entire series.

You can delete custom document properties, but you cannot delete a built-in document property.

Example

This example deletes cells A1:D10 on Sheet1, and shifts remaining cells to the left.

Worksheets("Sheet1").Range("A1:D10").Delete shift:=xlToLeft

This example deletes oval one on Sheet1.

Worksheets("Sheet1").Ovals(1).Delete

This example deletes all graphic objects and controls (check boxes, buttons, and so on) on Sheet1.

Worksheets("Sheet1").DrawingObjects.Delete

This example deletes every worksheet in the active workbook without displaying the confirmation dialog box.

Application.DisplayAlerts = False
For Each w In Worksheets
    w.Delete
Next w
Application.DisplayAlerts = True

This example sorts the data in the first column on Sheet1 and then deletes rows that contain duplicate data.

Worksheets("Sheet1").Range("A1").Sort _
        key1:=Worksheets("Sheet1").Range("A1")
Set currentCell = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(currentCell)
    Set nextCell = currentCell.Offset(1, 0)
    If nextCell.Value = currentCell.Value Then
        currentCell.EntireRow.Delete
    End If
    Set currentCell = nextCell
Loop