How to find column letter in cell address using VBA Excel – debugvba

find column letter in cell address vba excel

Objective

To find column letter in cell address using VBA Excel.

Approach

Here we have a row which contains multiple values and we are trying to find the characters in the last populated cell adress. So first we have found the last populated cell and then found the characters in cell address.

There are 2 approaches which are shown below to find the last characters in column address –

Use Split Function

Here first we have found the cell address and stored that in a variable i.e. LastCellAddress, and then we have applied split function on variable LastCellAddress. In the last we have printed the characters in the column using array item LastColumnAlphabet(1) as shown below.

 

Sub FindLastColumnCharacter_1()

Dim LastCellAddress  As String

LastCellAddress = Range("A1").End(xlToRight).Address

LastColumnAlphabet = Split(LastCellAddress, "$")

'Print the alphabet in last column

Debug.Print LastColumnAlphabet(1)

End Sub


 

We can see the output of both the code snippets in the immediate window.

Use Mid Function

It is similar to the approach above. First, we have found the address of last populated cell and stored it in variable LastCellAddress, and then we have found the position of 2nd dollar ($) sign in the variable LastCellAddress  and stored the result in variable DollarSignPosition. Then we have used the mid function to fetch the characters from the variable LastCellAddress.

 

Sub FindLastColumnCharacter_2()

Dim LastCellAddress  As String
Dim ColumnAlphabet As String

LastCellAddress = Range("A1").End(xlToRight).Address
DollarSignPosition = InStr(2, LastCellAddress, "$", vbTextCompare)

'Finding the alphabets in column address
ColumnAlphabet = Mid(LastCellAddress, 2, DollarSignPosition - 2)

'Print the alphabet in last column
Debug.Print ColumnAlphabet

End Sub

 

In the example below we can see that we had 8 populated cells in row 1.So last cell on right side is H8. So, if we execute the above shown VBA code, we get “H” as character in cell address. In the image below we can see the output in the immediate window.

 

find column alphabet in cell address vba excel

 

 

find column alphabet in cell address vba excel

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function
https://docs.microsoft.com/en-us/office/vba/api/excel.range.address
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/mid-function