# Range Address from Number to Letter

Sometimes, before I realize it (and, Iâ€™m sure, others too) I do a bunch of work, that turns out that I didnâ€™t need to do before. The RangeAddress function that I created is a perfect example of this. This function goes from a column number to a letter and returns the whole string range address. I started off with someones elseâ€™s code. I then made it work for me. Their code only worked for Excel 2003 and below. The code below will work for All Excel versions. But there is a better way! The better way is in the RRange function I posted about before. Below this code Iâ€™ll show you the basic gist on how to go about it.

'RangeAddress
' --------------------------------------------------------------
'
'Arguments: lRow Row number of first row.
' lColumn Column number of first column.
'
'Date Developer History
'--------------------------------------------------------------
'Feb 14, 2011 Jon Nyman Initial version
'
Public Function RangeAddress(ByRef lRow As Long, ByRef lColumn As Long, _
Optional ByVal lRow2 As Long = 0, Optional ByVal lColumn2 As Long = 0 _
, Optional ByVal sAddress As String = vbNullString) As String
Dim i As Integer
Dim lColumnNumber As Long
Dim sColumn As String, sColumn2 As String, sColumnLetter As String

For i = 0 To 1
If i = 0 Then
lColumnNumber = lColumn
Else
lColumnNumber = lColumn2
End If
sColumnLetter = vbNullString
If lColumnNumber < 27 Then
sColumnLetter = Chr(lColumnNumber + 64)
ElseIf lColumnNumber < 703 Then
sColumnLetter = Chr((lColumnNumber â€“ 1) \ 26 + 64) & Chr(((lColumnNumber â€“ 1) Mod 26) + 65)
Else
sColumnLetter = Chr((lColumnNumber â€“ 27) \ 676 + 64) & Chr(((lColumnNumber â€“ 27) Mod 676) \ 26 + 65) & _
Chr(((lColumnNumber â€“ 1) Mod 26) + 65)
End If
If i = 1 Then
sColumn2 = sColumnLetter
Exit For
Else
sColumn = sColumnLetter
If lColumn2 = 0 Then Exit For
End If
Next i

If lRow2 > 0 And lColumn2 > 0 Then
RangeAddress = "$" & sColumn & "$" & lRow & ":$" & sColumn2 & "$" & lRow2
If CountStringOccurance(sAddress, ":") = 1 Then
sAddress = Right$(sAddress, Len(sAddress) â€“ i + 1) RangeAddress = "$" & sColumn & "$" & lRow & sAddress Else RangeAddress = "$" & sColumn & "$" & lRow End If Else RangeAddress = "$" & sColumn & "$" & lRow End If RangeAddress_Exit: On Error Resume Next Exit Function RangeAddress_Error: GoTo RangeAddress_Exit End Function  A better way: Sub Example() Dim iColumn As Integer Dim rRng As Range Dim sLetter As String Dim wks As Worksheet 'Initialize variables iColumn = 35 Set wks = ActiveSheet 'Return column letter 'Note: it is important to work with the a worksheet, ' since if the active sheet is a chart then the Cells function would throw an error. sLetter = wks.Cells(1, iColumn).Address(False, False) sLetter = Left$(sLetter, Len(sLetter) â€“ 1)

'Bypass even bothering to get the letter in the first place
With wks
Set rRng = .Range(.Cells(1, iColumn), .Cells(1, iColumn + 3))
End With

'Results
Debug.Print "Column Letter: " & sLetter & vbNewLine & "Range Address: " & rRng.Address

End Sub


Results from Example test above:

Column Letter: AI
Range Address: $AI$1:$AL$1