Excel COLUMNS Function
The Excel COLUMNS function returns the number of columns in a specified array
Example: Excel COLUMNS Function
|
=COLUMNS(B2)
|
Result in cell B5 (1) - returns the number of columns in the selected reference.
|
|
=COLUMNS(D5:J5)
|
Result in cell B6 (7) - returns the number of columns in the selected reference.
|
|
=COLUMNS(1:1)
|
Result in cell B7 (16,384) - returns the number of columns in an entire row.
|
|
=COLUMNS(Column_Defined_Name)
|
Result in cell B8 (3) - returns the number of columns in the defined name named Columns_Defined_Name that comprises a G3:I7 range.
|
METHOD 2. Excel COLUMNS function using the Excel built-in function library
EXCEL
Formulas tab > Function Library group > Lookup & Reference > COLUMNS > populate the input box
| =COLUMNS(B2) Note: in this example we are populating the Array input box with a single cell reference. |
 |
| =COLUMNS(D5:J5) Note: in this example we are populating the Array input box with a single range reference. |
 |
| =COLUMNS(1:1) Note: in this example we are populating the Array input box with an entire row. |
 |
| =COLUMNS(Columns_Defined_Name) Note: in this example we are populating the Array input box with a defined name named Columns_Defined_Name that comprises G3:I7 range. |
 |
Sub Excel_Columns_Function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("COLUMNS")
'apply the Excel COLUMNS function
ws.Range("B5") = Range("B2").Columns.Count
ws.Range("B6") = Range("D5:J5").Columns.Count
ws.Range("B7") = Range("1:1").Columns.Count
ws.Range("B8") = Range("Columns_Defined_Name").Columns.Count
ws.Range("B5") = Range("B2").Columns.Count
ws.Range("B6") = Range("D5:J5").Columns.Count
ws.Range("B7") = Range("1:1").Columns.Count
ws.Range("B8") = Range("Columns_Defined_Name").Columns.Count
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 COLUMNS.
Defined Name: Have a defined name named Columns_Defined_Name that comprises (G3:I7) range.
Worksheet Name: Have a worksheet named COLUMNS.
Defined Name: Have a defined name named Columns_Defined_Name that comprises (G3:I7) range.
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.