Column related procedures in class Range (Excel VBA)

This page presents procedures from class Range related to the theme Column: Column, ColumnDifferences, Columns, EntireColumn and TextToColumns

Column

Returns the number of the first column in the first area in the specified range.

Column A returns 1, column B returns 2, and so on. To return the number of the last column in the range, use the following expression. myRange.Columns(myRange.Columns.Count).Column

Dim lngColumn As Long
lngColumn = ActiveCell.Column

ColumnDifferences

Returns a Range object that represents all the cells whose contents are different from the comparison cell in each column.

ColumnDifferences (Comparison)

Comparison: A single cell to compare to the specified range.

Dim rngColumnDifferences As Range
Set rngColumnDifferences = ActiveCell.ColumnDifferences(Comparison:=)

Columns

Returns a Range object that represents the columns in the specified range.

To return a single column, use the Item property or equivalently include an index in parentheses. For example, both Selection.Columns(1) and Selection.Columns.Item(1) return the first column of the selection. When applied to a Range object that is a multiple-area selection, this property returns columns from only the first area of the range. For example, if the Range object has two areas—A1:B2 and C3:D4—Selection.Columns.Count returns 2, not 4. To use this property on a range that may contain a multiple-area selection, test Areas.Count to determine whether the range contains more than one area. If it does, loop over each area in the range. The returned range might be outside the specified range. For example, Range("A1:B2").Columns(5).Select returns cells E1:E2. If a letter is used as an index, it is equivalent to a number. For example, Range("B1:C10").Columns("B").Select returns cells C1:C10, not cells B1:B10. In the example, "B" is equivalent to 2. Using the Columns property without an object qualifier is equivalent to using ActiveSheet.Columns. For more information, see the Worksheet.Columns property.

Dim rngColumns As Range
Set rngColumns = ActiveCell.Columns

EntireColumn

Returns a Range object that represents the entire column (or columns) that contains the specified range.

Dim rngEntireColumn As Range
Set rngEntireColumn = ActiveCell.EntireColumn

TextToColumns

Parses a column of cells that contain text into several columns.

The following table shows the results of importing text into Excel for various import settings. Numeric results are displayed in the rightmost column.

TextToColumns (Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

ActiveCell.TextToColumns

Arguments

The following arguments are optional

Destination (Range) - A Range object that specifies where Microsoft Excel will place the results. If the range is larger than a single cell, the top left cell is used.

DataType (XlTextParsingType) - The format of the text to be split into columns.

Possible Values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

TextQualifier (XlTextQualifier) - Specifies whether to use single, double, or no quotes as the text qualifier.

Possible Values are xlTextQualifierDoubleQuote - Double quotation mark ("), xlTextQualifierNone - No delimiter, xlTextQualifierSingleQuote - Single quotation mark (').

ConsecutiveDelimiter (Boolean) - True to have Excel consider consecutive delimiters as one delimiter. The default value is False.

Tab (Tab) - True to have DataType be xlDelimited and to have the tab character be a delimiter. The default value is False.

Semicolon (XlTextParsingType) - True to have DataType be xlDelimited and to have the semicolon be a delimiter. The default value is False.

Possible Values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

Comma (XlTextParsingType) - True to have DataType be xlDelimited and to have the comma be a delimiter. The default value is False.

Possible Values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

Space (XlTextParsingType) - True to have DataType be xlDelimited and to have the space character be a delimiter. The default value is False.

Possible Values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

Other (XlTextParsingType) - True to have DataType be xlDelimited and to have the character specified by the OtherChar argument be a delimiter. The default value is False.

Possible Values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

OtherChar - Required if Other is True; the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored

FieldInfo (XlColumnDataType) - An array containing parse information for the individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

Here you can find possible values for XlColumnDataType.

DecimalSeparator (String) - The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.

ThousandsSeparator (String) - The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.

TrailingMinusNumbers - Numbers that begin with a minus character