How do I get the last column address

Question

I am using the following code to get the last column number but I am trying to get he last column address. So if the last column is 5 then the result I am looking for is "F"

Sub LastColumnInOneRow()
Dim LastCol As Integer
With Sheets("Current Pipeline")
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
MsgBox LastCol

End Sub

Why? You can just use `Cells()` which allows the use of the column index and does not need to be converted to a string just to be converted back to an index. — Scott Craner, Oct 31 '22 at 14:28

score 0 · Answer 1 · answered Oct 31 '22 at 14:47

0

This has been discussed MUCH in the past.
From Function to convert column number to letter? :

Sub sub1()
  Dim LastCol&, LastColLtr$
  LastCol = 6
  LastColLtr = Split(Columns(LastCol).Address, "$")(2)
End Sub

answered Oct 31 '22 at 14:47

dcromley

1,373
1
8
23

3

Please consider voting to close as duplicate, instead of (re)posting the same information in yet another answer. – BigBen Oct 31 '22 at 14:49
1

And we should make sure that the OP is wanting this for a legitimate reason(of which I cannot think of any) and stop perpetuating the need to convert column references to letters in vba, which is poor coding as well as very slow. – Scott Craner Oct 31 '22 at 14:57
Scott, I am not sure why you are saying this. I am not "perpetuating" anything, I am simply asking for help. Clearly I am not a professional coder and would not be familiar with good coding so all my coding is poor. This forum is to help people not to chastise them for "poor" coding – MEC Oct 31 '22 at 15:00
@MEC I am not chastising you. I am chastising dcromley. I asked the question in the comments. If you had said it is to use the column reference in a `Range` object I would have written up an answer showing/teaching the proper way to use and handle column Index and then added a link to another question as to why using string column letters slow the code. I knew about the dup and could easily closed it as such, but was waiting for your response, which is what dcromley should have done. – Scott Craner Oct 31 '22 at 15:03
apologies Scott, I did not understand the exchange of comments, my apologies. – MEC Oct 31 '22 at 15:13
In any case I was able to get the answer I needed from https://www.mrexcel.com/board/threads/how-do-i-get-the-last-column-address.1220767/post-5969704 – MEC Oct 31 '22 at 15:14
@MEC take a moment and look into `Cells()` object. If you are converting a number to a letter to use in a `Range()` object, just note that vba converts that back to an index. All this converting is slow and can be avoided with the proper use of the `Cells()` object. But I am glad you got your answer none the less. – Scott Craner Oct 31 '22 at 15:17
@BigBen Got it. – dcromley Oct 31 '22 at 19:32

How do I get the last column address

1 Answers1