Class ListColumn (Excel VBA)

The class ListColumn represents a column in a table. To use a ListColumn class variable it first needs to be instantiated, for example

Dim lstcol as ListColumn
Set lstcol = ActiveCell.ListObject.ListColumns(Index:=1)

For Each

Here is an example of processing the ListColumn items in a collection.

Dim lstcol As ListColumn
For Each lstcol In ActiveCell.ListObject.ListColumns
	
Next lstcol

DataBodyRange

Returns a Range object that is the size of the data portion of a column.

The returned object excludes the Header and Totals cell.

Dim rngDataBodyRange As Range
Set rngDataBodyRange = ActiveCell.ListObject.ListColumns(1).DataBodyRange

Delete

Deletes the column of data in the list.

This method does not remove the column from the sheet. If the list is linked to a Microsoft SharePoint Foundation site, the column cannot be removed from the server, and an error is generated.

ActiveCell.ListObject.ListColumns(1).Delete

Index

Returns a Long value that represents the index number of the ListColumn object within the ListColumns collection.

Dim lngIndex As Long
lngIndex = ActiveCell.ListObject.ListColumns(1).Index

Name

Returns or sets a String value that represents the name of the list column.

The value of this property is also used as the display name of the list column. This name must be unique within the list.

ActiveCell.ListObject.ListColumns(1).Name =

Range

Returns a Range object that represents the range to which the specified list object applies.

Dim rngRange As Range
Set rngRange = ActiveCell.ListObject.ListColumns(1).Range

Total

Returns the Total row for a ListColumn object.

Dim rngTotal As Range
Set rngTotal = ActiveCell.ListObject.ListColumns(1).Total

TotalsCalculation

Determines the type of calculation in the Totals row of the list column based on the value of the XlTotalsCalculation enumeration. Here you can find possible values for XlTotalsCalculation.

The Totals row doesn't need to be showing to set this property. There is no fixed "default" value for this property. Excel may change the state of this property as other columns are added or deleted.

ActiveCell.ListObject.ListColumns(1).TotalsCalculation = xlTotalsCalculationAverage

XPath

Returns an XPath object that represents the XPath of the element mapped to the specified Range object. The context of the range determines whether the action succeeds or returns an empty object.

The XPath property is valid when the range that it contains meets the following conditions:

Dim xphXPath As XPath
Set xphXPath = ActiveCell.ListObject.ListColumns(1).XPath