Column Letters

[mmetzinger]

Ok, I know there have been like a billion questions about this but there doesn't seem to be a definitive answer and I have come to Mr. Excel to get it. I am using Office 2010 and am responsible for sorting out this huge census files we get from our customers. My issue is I write one Macro and then generalize it to be used on many different companies.

So to accomplish this I figured out how to find the last row which was easy with:
Dim LastRow as string
LastRow = "A" & Range("A1").End(xlDown).Select
'I just wrote that from memory so it may be a little wrong

But I need to be able to find the last column and store that in a variable that I can call later. What I have found is that you can get a column number with:
Range("A1").End(xlToRight).Select

But I can't figure out how to get the column letter so I can do something like:
Dim LastColumn as string
Lastcolumn = Range("A1").End(xlToRight).Select 'but column letter

My goal is to have it where in operations later I can just give it LastColumn instead of a arbitrary column that I choose myself like CC or something.

Thanks for any help.

 

[texasalynn]

to get the last column


LCol = ActiveSheet.Range("IV1").End(xlToLeft).Column

then you can use it with
Cells(1, LCol)

 

[VoG]

If you use Cells notation you can work with column numbers:

LC = Cells(1, Columns.Count).End(xlToLeft.Columns)
Cells(1, LC).Select

 

[jaslake]

I prefer to work with Column Letters so I use this

Sub LastColumn()
    Dim LC As String
    LC = ColumnLetter(Range("IV1").End(xlToLeft).Column)
End Sub
 
 
Private Function ColumnLetter(ByVal iCol As Long) As String
'=========================================
'Input column number, output column Letter
'=========================================
'A-Z
    If iCol <= 26 Then
        ColumnLetter = Chr(iCol + 64)
        Exit Function
    Else
        ColumnLetter = Chr(Int((iCol - 1) / 26) + 64) & _
                Chr(((iCol - 1) Mod 26) + 65)
    End If
End Function

John

 

[texasalynn]

ok and then for excel 2007 you have to have a third for the three letter columns

 

[mmetzinger]

Wow, thanks for all the help everyone.

 

[jaslake]

Yes, texasalynn is correct. The ColumnLetter Function posted works in pre 2007 Excel. Hence thr reference to

Range("IV1")

A Column Letter Function for Excel 2007 and later is

Function ColumnLetter(ColumnNumber As Long) As String
' From [URL]http://www.craigmurphy.com/blog/?p=150[/URL]
' Works in Excel 2007
Dim ColNum As Integer
Dim ColLetters As String
ColNum = ColumnNumber
ColLetters = ""
Do
ColLetters = Chr(((ColNum - 1) Mod 26) + 65) & ColLetters
ColNum = Int((ColNum - ((ColNum - 1) Mod 26)) / 26)
Loop While ColNum > 0
ColumnLetter = ColLetters
End Function

John

 

[mirabeau]

To get the column letter(s) from column number, try

Sub letterfromnumber()
Dim colno&
colno = InputBox("Enter column number", 2)
MsgBox Split(Columns(colno).Address(0, 0), ":")(0)
End Sub

If you're using this as part of a larger code, it's obviously v. easy to incorporate this as a one-liner..., or use it as a user-defined function ... or whatever

 

[hiker95]

mmetzinger,

Dim lngLastCol As Long
Dim strColName As String


''last used column in row 1
lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

''Or:

''last used column in the active sheet
lngLastCol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column



strColName = Replace(Cells(1, lngLastCol).Address(0, 0), 1, "")

''Or:

strColName = Split(Columns(lngLastCol).Address, "$")(2)

 

[jaslake]

hi hiker95

I like it. This goes in my toolbox.

John