Vba convert column number in name in Excel

For example, I have a need to define a range in my code like Range("A2:AG37") where AG is the last column in my data table. I have figured out the last column no (33 in this case) using code - how can I convert this number to "AG".

This is very useful when you want to indicate to your user the exact position of a cell in the sheet.

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub ColNoToColLetter()
  3. Dim lngColNo As Long
  4. Dim sColumnName As String
  5. ''
  6. lngColNo = 33
  7. sColumnName = Replace(Replace(Cells(1, lngColNo).Address, "1", ""), "$", "")
  8. ''
  9. End Sub


a) Cells(1, lngColNo).Address is used to refer to the column of interest in row one (in the example case it refers to $AG$1).

Inside Replace function is used to remove row 1 reference from cell address.

The second Replace function is used to remove dollar from the address reference so that only Column Name remains.

c) If there are duplicate records, they are removed and a prompt appears indicating how many duplicate records were found and removed and how many unique values remain.


You can find similar Excel Questions and Answer hereunder

1) How can I update a listbox based on data in a list using VBA?

2) How can I check if a file exists in a folder using VBA?

3) How to find the column number from the cell address in VBA

4) Vba clear the contents of an entire sheet in Excel

5) Can I assign a NAME to a constant (similar to declaring constants in coding environment like VBA)?

6) How to add a link in a sheet to another sheet

7) How can I get users to select a file for processing using my macro?

8) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

9) How can I get the day number of the year for a date - for example 5 for Jan 5, 33 for Feb 2?

10) How to find the cell address from the column number in VBA


Here the previous and next chapter