how to extract column letter of a cell & select cell by referencing its column number

If I have a named cell in the worksheet, "myCell", how can I extract it's column letter using VB?

Also, in addition to the above, if I have a column number (not letter) and a row number of a cell, how can I use that info to select that cell in my worksheet? So for example, I want to be able to select cell C5, (and all my sub knows is the cell's row, which is 5, and column number which is 3).

Thank you.

Hi luv2glyd
use "Range("myCell").Column"

Sub tested()
Debug.Print Range("myCell").Column
Cells(5, 3).Select
End Sub

Cells( Row number, column Number)

a1 is cells (1,1)

Thank you Pike. That answers my second quesiton. On occasion, I need to use actual column letter in my code vs. column number (for example: letter of cell ADB4 is "ADB"). How can I extract that info?

Thank you

Hi luv2glyd

what are you trying to do?
you can use
cells(4,"ADB")

but where is ADB4 comming from?

I can use column numbers most of the time, but someitmes when i work with a lot of data (which spans way to the right in the worksheet), it'd be nice to use column letters, so when I look at the code and the data location in workbook, its easier to check the code and the location of data for consistency, to make sure everything is alligned and working correctly. So rather then figuring out what the column number of a cell is (in example below column number of cell DK is 115), it'd be nice to reference that cell by column letter. Here's just one of the examples of the code I have:

data_start_row = Range("sort_table_start").Row
data_start_column = Range("sort_table_start").________ 'need code to get column letter

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "DK" & data_start_row & ":" & "DK" & data_end_row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

So for the example above, I'd like to be able to use data_start_column in place of "DK" and extract this column letter in a way similar to:

data_start_column = Range("sort_table_start").________

Also, many times the way I write code, for simplicity sake, is by recording a macro, and then going in and modifying it to do what I need it to do. Excel records everything with references to column letters vs. column numbers, so when I go in to tweak something, it'd be nice to know how to get the column letter, and then just insert it in the macro

Try these functions

Function ColumnLetter(rngTarget As Range) As String
    Dim strCol As String
    
    strCol = rngTarget.Address
    strCol = Right(strCol, Len(strCol) - 1)
    strCol = Left(strCol, InStr(1, strCol, "$") - 1)
    
    ColumnLetter = strCol
End Function

Call in VBa

MyVar = ColumnLetter(Range("A1"))

Or as a UDF in Excel
=ColumnLetter(A1)

Function ColumnNumberToString(ColumnNumber As Integer) As String
    Dim strCol As String
    
    strCol = Cells(1, ColumnNumber).Address
    strCol = Right(strCol, Len(strCol) - 1)
    strCol = Left(strCol, InStr(1, strCol, "$") - 1)
    
    ColumnNumberToString = strCol
End Function

Call in VBa

MyVar = ColumnNumberToString(1)

Hope this helps

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Great! Thank you every one. I think I like the last two solutions the best - short and sweet to get what I need. Sorry for the late reply - was gone yesterday.