vba convert column number in name in Excel

Q154. In Excel, how can I convert Column numbers into Column names for use in range definition?

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.

excel vba convert column number in name

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

Description:

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 find number of months that has elapsed given 2 dates?

2) How can I extract file name from a full path including folder path and file name?

3) How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

4) How can I add Trendline to a chart using VBA?

5) How can I set FreezePanes in a certain range using VBA?

6) How can I dynamically add a hyperlink using VBA?

7) Determine if hyperlinks are valid in Excel

8) Can I apply formatting to a number concatenated with a Text?

9) How can I get input from user through a prompt and assign user's input to a cell?

10) How can I sort data using VBA?

11) How can I set up a dynamic named range that expands automatically when new items are added to the list?

12) How can I find the number of working days between 2 dates using VBA?

13) How can I protect / unprotect WorkSheet using VBA?

14) Split one long column into multiple smaller columns in Excel

15) How do I enter a formula in a cell using VBA (using Relative Reference)?