Return last column number in a range
This tutorial shows how to return the last column number from a selected range using an Excel formula or VBA
Example: Return last column number in a range
|
This formula uses the Excel COLUMN functions to return the column number of the first column number and the Excel COLUMNS function to return the total number of columns in the selected range. Adding these together and subtracting a value of one will return the last column number in a selected range which returns a value of 4, being column D.
|
Sub Return_last_column_number_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 number in a range
ws.Range("F5") = rng.Column + rng.Columns.Count - 1
ws.Range("F5") = rng.Column + rng.Columns.Count - 1
End Sub
EXPLANATION
This tutorial shows how to return the last column number from a selected range through the use of an Excel formula or VBA.
Using the COLUMN and COLUMNS function in both the Excel and VBA methods it will return the last column number in a selected range. The COLUMN function returns the first column number from the selected cell or range and the COLUMNS function returns the total number of columns in a range. Adding these two values and subtracting a value of one will return the last column number in a range.
FORMULA
=COLUMN(range)+COLUMNS(range)-1
=COLUMN(range)+COLUMNS(range)-1
ARGUMENTS
range: A range of cells for which you want to return the last column number.
range: A range of cells for which you want to return the last column number.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| 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 |