Excel VBA: Counting Columns In a Table

This Excel VBA macro shows a message box with a table columns count.

Macro Example

Sub GettingTableColumnsCount()

  MsgBox ActiveSheet.ListObjects(1).ListColumns.Count

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”.

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

ActiveSheet.ListObjects (Worksheet.ListObjects)

  ActiveSheet.ListObjects...

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

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

  ActiveSheet.ListObjects(1)...

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

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).ListColumns (ListObject.ListColumns )

  ActiveSheet.ListObjects(1).ListColumns...

The ListObjects(1).ListColumns (ListObject.ListColumns property) returns the ListColumns collection object that represents all the columns in the ListObject object.

ListColumns.Count

  ActiveSheet.ListObjects(1).ListColumns.Count

The Columns.Count property returns the long integer value that represent columns count.

Sub GettingTableColumnsCount()

  MsgBox ActiveSheet.ListObjects(1).ListColumns.Count

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-get-table-columns-count.php

Apply to

• Excel 2013
• Excel 2010
• Excel 2007