Return first column name in a range
This tutorial shows how to return the first column name from a selected range using an Excel formula or VBA
Example: Return first column name in a range
|
=SUBSTITUTE(ADDRESS(1,COLUMN(B5:C10),4),"1","")
|
This formula uses a combination of the Excel SUBSTITUTE, ADDRESS and COLUMN functions to return the column name of the first cell in the selected range. In this example the first column in the range is column B.
|
Sub Return_first_column_name_in_range()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the first column name in a range
ws.Range("E5") = Application.WorksheetFunction.Substitute(ws.Cells(1, ws.Range("B5:C10").Column).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")
ws.Range("E5") = Application.WorksheetFunction.Substitute(ws.Cells(1, ws.Range("B5:C10").Column).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")
End Sub
EXPLANATION
This tutorial shows how to return the first column name from a selected range through the use of an Excel formula or VBA.
Using the SUBSTITUTE, ADDRESS and COLUMN functions in both the Excel and VBA methods it will return the first column name in a selected range.
FORMULA
=SUBSTITUTE(ADDRESS(1,COLUMN(range),4),"1","")
=SUBSTITUTE(ADDRESS(1,COLUMN(range),4),"1","")
ARGUMENTS
range: A range of cells for which you want to return the first column name.
range: A range of cells for which you want to return the first column name.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return column number of a specific value | How to return a column number of a specific value using Excel and VBA | |
| Return column name of a specific value | How to return a column name of a specific value using Excel and VBA | |
| Return column name of a cell where the formula is written | How to return a column name of a cell where the formula is written using Excel and VBA | |
| Return column name of a specific cell | How to return a column name of a specific cell using Excel and VBA | |
| Return first column number in a range | How to return the first column number from a selected range using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| COLUMN Function | The Excel COLUMN function returns the first column number of the selected reference | |
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string | |
| ADDRESS Function | The Excel ADDRESS function returns a cell reference as a string, based on a row and column number |