Capturing Column as Alpha

Lisa928

Board Regular
Joined
Jun 13, 2002
Messages
173
I am writing code and need to capture the column my cursor is in, but as an ALPHA, not numeric. The only way I currently know how to is using 'currentcell = ActiveCell.Column' but it is numeric.

Can anyone help?
Thanks in advance for your time!
Lisa
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
If it's the first 26 columns, an easy way is
mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Activecell.Column,1)
 
Upvote 0
Hi,

Do you make references to columns AA and above? Is that why the MID option is not working for you? Bob's suggestion is clever.

There have been a few really nice UDFs to do this posted to the board a while ago. I can't think of the thread off hand, though. In any event, here is a quick routine which should do what you'd like. It can be shortened considerably, too.

Also, there are other approaches that work, so let us know if something else is needed.

<pre>Sub test()
Dim ColLetter As String
Dim ColNum As Integer


ColNum = ActiveCell.Column
ColLetter = Left(ActiveSheet.Cells(1, ColNum).Address(False, False), _
(ColNum <= 26) + 2)

MsgBox ColLetter
End Sub</pre>
 
Upvote 0
This is the easiest 1-liner I can think of:

Left(ActiveCell.EntireColumn.Address(columnabsolute:=False) & "", Application.WorksheetFunction.Find(":", x1) - 1)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top