Excel VBA: Deleting the First Row in a Table

This Excel VBA macro deletes the first row in a table on an active sheet.

Macro Example

Sub Deleting1stTableRow()

  ActiveSheet.ListObjects(1).ListRows(1).Delete

End Sub

Description

[Application.]ActiveSheet

  ActiveSheet...

The [Application.]ActiveSheet property returns an active Worksheet or Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

The ActiveSheet property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-ActiveSheet

ActiveSheet.ListObjects (Worksheet.ListObjects)

  ActiveSheet.ListObjects...

The ActiveSheet.ListObjects (Worksheet.ListObjects property) returns a ListObjects collection object on the active worksheet.

obj-Excel-Worksheet-ListObjects

ListObjects(1) (ListObjects._Default(1) )

  ActiveSheet.ListObjects(1)...

The ListObjects._Default property returns a single ListObject object from a ListObjects collection object.

obj-Excel-ListObjects-_Default

And the ListsObjects._Default(1) represents the first ListObject object.

The _Default property is a default member of the ListObjects collection object. We can see a little blue marble beside its icon in the Object Browser.
So we can omit the ._Default .

ListObjects(1).ListRows (ListObject.ListRows)

  ActiveSheet.ListObjects(1).ListRows...

The ListObjects(1).ListRows (ListObject.ListRows property) returns the ListRows collection object that represents the all table rows.

obj-Excel-ListObject-ListRows

ListRows(1) (ListRows._Default(1) )

  ActiveSheet.ListObjects(1).ListRows(1)...

The ListRows._Default property returns a single ListRow object from the ListRows collection object.

obj-Excel-ListRows-_Default

And the ListsRows._Default(1) represents the first ListRow object.

The _Default property is a default member of the ListRows collection object. We can see a little blue marble beside its icon in the Object Browser.
So we can omit the ._Default .

ListRows(1).Delete (ListRow.Delete )

  ActiveSheet.ListObjects(1).ListRows(1).Delete

Finally, the ListRows(1).Delete (ListRow.Delete method) deletes the row.

obj-Excel-ListRow-Delete

Sub Deleting1stTableRow()

  ActiveSheet.ListObjects(1).ListRows(1).Delete

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-table-delete-a-row.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007