Delete Method

Applies To

Axis object, AxisTitle object, Characters object, Chart object, ChartObject object, ChartObjects collection object, Charts collection object, ChartTitle object, Comment object, CustomView object, DataLabel object, DataLabels collection object, DataTable object, DownBars object, DropLines object, ErrorBars object, FormatCondition object, FormatConditions collection object, Gridlines object, HiLoLines object, HPageBreak object, Hyperlink object, Hyperlinks collection object, LeaderLines object, Legend object, LegendEntry object, LegendKey object, Name object, OLEObject object, OLEObjects collection object, Parameters collection object, PivotField object, PivotFormula object, PivotItem object, Point object, QueryTable object, Range object, RecentFile object, Scenario object, Series object, SeriesLines object, Shape object, ShapeNodes collection object, ShapeRange Collection object, Sheets collection object, SoundNote object, Style object, TickLabels object, Trendline object, UpBars object, Validation object, VPageBreak object, Worksheet object, Worksheets collection object.

Description

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

Syntax 1

expression.Delete

Syntax 2

expression.Delete(Shift)

expression Required. An expression that returns an object in the Applies To list.

Shift Optional Variant. Used only with Range objects. Specifies how to shift cells to replace deleted cells. Can be one of the following XlDeleteShiftDirection constants: xlShiftToLeft or xlShiftUp. If this argument is omitted, Microsoft Excel decides based on the shape of the range.

Remarks

Deleting a Point or LegendKey object 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 the remaining cells to the left.

Worksheets("Sheet1").Range("A1:D10").Delete Shift:=xlShiftToLeft

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