To find column letter in cell address using VBA Excel.
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.