Excel COLUMN Function
The Excel COLUMN function returns the first column number of the selected reference
Example: Excel COLUMN Function
=COLUMN()
|
Result in cell B5 (2) - returns the column number of the cell that the formula is written in.
|
=COLUMN(D5)
|
Result in cell B6 (4) - returns the column number of column D.
|
=COLUMN(E3:G7)
|
Result in cell B7 (5) - returns the column number of the first cell in the selected range, being column E.
|
=COLUMN(Column_Defined_Name)
|
Result in cell B8 (2) - returns the column number of the Column_Defined_Name defined name that comprises cell B10.
|
METHOD 2. Excel COLUMN function using the Excel built-in function library
EXCEL
Formulas tab > Function Library group > Lookup & Reference > COLUMN > populate the input box
=COLUMN() Note: in this example we are leaving the input box empty which will return the column number of the cell that the formula is written in. |
=COLUMN(D5) Note: in this example we are populating the Reference input box with a single cell reference. |
=COLUMN(E3:G7) Note: in this example we are populating the Reference input box with a single range reference. |
= COLUMN(Column_Defined_Name) Note: in this example we are populating the Reference input box with a defined name that comprises cell B10. |
Sub Excel_Column_Function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("COLUMN")
'apply the Excel COLUMN function
ws.Range("B5") = ws.Range("B5").Column
ws.Range("B6") = ws.Range("D5").Column
ws.Range("B7") = ws.Range("E3:G7").Column
ws.Range("B8") = ws.Range("Column_Defined_Name").Column
ws.Range("B5") = ws.Range("B5").Column
ws.Range("B6") = ws.Range("D5").Column
ws.Range("B7") = ws.Range("E3:G7").Column
ws.Range("B8") = ws.Range("Column_Defined_Name").Column
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named COLUMN.
Defined Name: Have a Column_Defined_Name defined name that comprises cell (B10).
Worksheet Name: Have a worksheet named COLUMN.
Defined Name: Have a Column_Defined_Name defined name that comprises cell (B10).
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("B5"), ("B6"), ("B7") and ("B8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
DESCRIPTION
The Excel COLUMN function returns the first column number of the selected reference.
The Excel COLUMN function returns the first column number of the selected reference.
SYNTAX
=COLUMN([reference])
=COLUMN([reference])
ARGUMENTS
reference: (Optional) A cell or range of cells for which you want the value returned.
reference: (Optional) A cell or range of cells for which you want the value returned.
ADDITIONAL NOTES
Note 1:If you do not provide a reference the COLUMN function will return the column number of the cell in which you have entered the formula.