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