Get column name from column number?

Question

I have several named columns on a sheet. I want to send the column number to a function that will return the column name.

For example, if column 1 is named "apple", I want to pass the column number 1 to a function that returns the column name "apple". My attempt:

Function getColName(colNumber As Integer) As String
'return column name when passed column number

    getColName = Cells(1, colNumber).Column.Name

End Function

How can I get this code to work?

Exarchate

Try getColName = Cells(1, colNumber).Value – Equipotential

Columns in excel do not have names. You can name a range, or as @Equipotential is saying find the value of the first cell in which usually is a label that describes the values in the column. – Bramlett

@Scott Craner, you misunderstand me. I have named the entire column 1 (or column A if you prefer) "apple" (or [apple] if you prefer). I want to send the column number (1 in this case) to a function that returns the name of the column. – Exarchate

Here is the answer to my question. Sorry for repeating a previous question – Exarchate

getColName = Cells(1, colNumber).EntireColumn.Name.Name – Oliveolivegreen

@Tim Williams, thank you, sir. – Exarchate

Does this answer your question? How do you get a Range to return its Name? – Excerpt

Answer 1

Try this one :

set cell_address = Worksheet("Sheet1").Cells(1,1)
MsgBox cell_address.address(RowAbsolute:=False, ColumnAbsolute=False) 

'returns A1 You can use it in Range("A1") as well and get more functionality.

**Note**: RowAbsolute:=False, ColumnAbsolute=False are optional 

for more information:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel

for non-vba: https://exceljet.net/formula/convert-column-number-to-letter

Shem

That doesn't answer the question. Your answer corresponds to that question. The current question is answered and solved there. – Excerpt

Answer 2

Sub GetColumnNameFromRange()
    Dim Colname As String
    For Each cell In Selection.Cells
        If cell.EntireColumn.Hidden = False Then
            Colname = Mid(cell.Address, 2, Len(cell.Address) - (Len(cell.Row) + 2))
            MsgBox Colname
        End If
    Next cell
End Sub

Psychopathist

Hi Pankaj! Thank you for contributing an answer to Stackoverflow. Kindly take note that you can improve upon the readability of your answer by inserting the code in a code-block. Kindly see: meta.#251861 – Ticker

Answer 3

ColumnNumber = i 'where i need to be a integer number > 0 and <= 16,384
ColumnName = cells(1,i).Address(False, False)

The parameters inside Adress() are related if the rows and columns references are going to be absolute or relatives. In this case above they'll be relatives.

And it's over. Simple like that.

Igorot

Answer 4

Dim ColAdr as String, ColNo as Long, ColName as String
Function ColName (ColNo as Long)
ColAdr = ThisWorkbook.Sheets("Sheet1").Cells(1, ColNo).Address(RowAbsolute:=True, ColumnAbsolute:=False)
ColName = Left(ColAdr, InStr(1, ColAdr, "$") - 1)
End function

Tobi

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Ceresin

Answer 5

i = Selection.Column 'GET ACTIVE CELL COLUMN NUMBER

ColumnNo = Split(ActiveCell.Offset(1, i + 20).Address, "$")(1) 'GET COLUMN NAME shifts the column index 20 columns to the right.

Balbo

The question is about getting the Name of the column, not the letter ... and why add 20 columns? – Baltic

Answer 6

If you need the column letter(s) from the index, here's a function I've written for that:

Function colNameOf(ByVal colindex As Long) As String
    
    Dim overAlphabetic, charId As Long
    charId = colindex Mod 26
    colindex = colindex - charId
    overAlphabetic = colindex / 26
    
    If charId = 0 Then
        charId = 26
        overAlphabetic = overAlphabetic - 1
    End If
    
    Dim overAlphStr As String
    overAlphStr = ""
    
    If overAlphabetic > 0 Then
        overAlphStr = colNameOf(overAlphabetic)
    End If
    
    Dim lastChar
    lastChar = ChrW(charId + 64)
    
    colNameOf = overAlphStr & lastChar
    
End Function

Note, that there is yet no error handling for values smaller than 1. In that case the function just returns any nonsense. DIY...

Boisleduc

This is not an answer to the question – Eldwon