Return the last used colum id

[Titian]

I'm attempting to identify the last populated COLUMN. The following snippet of code returns the number 37, how do I translate 37 into column "AK"?

LC = Cells(9, Columns.Count).End(xlToLeft).Column

Or maybe you have a better way of achieving my requirement.

Thanks

 

[JackDanIce]

Can you expand on why you need it converted?

Cells(1, 37).Address returns "AK1"
Range("AK" & 1) is equivalent to Cells(1, 37) - In Range the column letter comes first, then the row number, in Cells, the first argument is the row number, the second is the column number.

 

[MickG]

Maybe!!!

Dim Lc As Long, col As String
Lc = Cells(9, Columns.Count).End(xlToLeft).Column
col = Split(Cells(9, Lc).Address(, 0), "$")(0)
MsgBox col

 

[Titian]

Hi Jack & Mick - thanks for your interest

Jack, I don't know where the last used column is, I'm generating a pivot table from a data set, the width of the PT will vary so I need to know the last column to be able to enter a Ranking formula below the totals generated by the PT

Mick - Great your "col" returns the last column id as required, just one mod please, I want to ignore the Total column of the PT so in effect, get the last column used the return the id of the one to the left of it.

Thanks again

 

[Rick Rothstein]

This line of code will assign the letter designation to the variable LC for the farthest right most cell displaying data...

[table="width: 500"]
[tr]
	[td]LC = Split(Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Address, "$")(1)[/td]
[/tr]
[/table]

Edit Note: I just saw Message #4 . Here is how to get the column to the left of the one calculated above...

[table="width: 500"]
[tr]
  [td]LC = Split(Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(, -1).Address, "$")(1)[/td]
[/tr]
[/table]

 

[JackDanIce]

For Mick's or Rick's suggestion, just add line:

LC = LC - 1

if you need to return the ID of the one to the left

 

[Rick Rothstein]

For ... Rick's suggestion, just add line:

LC = LC - 1

if you need to return the ID of the one to the left

No, my code is returning the column letter designation, not the column number. I have added an Edit Note to my original message with the adjustment for my code line.

 

[JackDanIce]

@Rick Rothstein: My mistake, misread your post as returning column number and must have replied as you made your edit

 

[Titian]

Thanks Rick, that's exactly what I need.