Return last column name in a range
This tutorial shows how to return the last column name from a selected range using an Excel formula or VBA
Example: Return last column name in a range
|
=SUBSTITUTE(ADDRESS(1,(COLUMN(B5:D10)+COLUMNS(B5:D10))-1,4),"1","")
|
This formula uses a combination of the Excel SUBSTITUTE, ADDRESS, COLUMN and COLUMNS functions to return the last column name in the selected range. In this example the last column in the range is column D.
|
Sub Return_last_column_name_in_range()
'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Analysis")
Set rng = ws.Range("B5:D10")
Set rng = ws.Range("B5:D10")
'return the last column name in a range
ws.Range("F5") = Application.WorksheetFunction.Substitute(ws.Cells(1, rng.Column + rng.Columns.Count - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")
ws.Range("F5") = Application.WorksheetFunction.Substitute(ws.Cells(1, rng.Column + rng.Columns.Count - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")
End Sub
EXPLANATION
This tutorial shows how to return the last column name from a selected range through the use of an Excel formula or VBA.
Using the SUBSTITUTE, ADDRESS, COLUMN and COLUMNS functions in both the Excel and VBA methods it will return the last column name in a selected range.
FORMULA
=SUBSTITUTE(ADDRESS(1,(COLUMN(range)+COLUMNS(range))-1,4),"1","")
=SUBSTITUTE(ADDRESS(1,(COLUMN(range)+COLUMNS(range))-1,4),"1","")
ARGUMENTS
range: A range of cells for which you want to return the last column name.
range: A range of cells for which you want to return the last column name.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return first column number in a range | How to return the first column number from a selected range using Excel and VBA | |
| Return first column name in a range | How to return the first column name 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 | |
| COLUMNS Function | The Excel COLUMNS function returns the number of columns in a specified array | |
| 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 |