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:
- Option Explicit
- Sub ColNoToColLetter()
- Dim lngColNo As Long
- Dim sColumnName As String
- ''
- lngColNo = 33
- sColumnName = Replace(Replace(Cells(1, lngColNo).Address, "1", ""), "$", "")
- ''
- 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.