Convert Column number to a letter? e.g. Column 203 = GU

[M Skabialka]

Given that I know how many columns are in a spreadsheet - how do I convert
that to a letter?
e.g. ActiveSheet.UsedRange.Columns.Count = 203

I need to refer to that column in this code
Range"A1:" & ??? & ActiveSheet.UsedRange.Rows.Count).Select
where ??? has been translated from 203 to GU

The number of columns and rows varies.
Thanks
Mich

 

[Gord Dibben]

Sub test()
Dim cc As Integer
cc = ActiveSheet.UsedRange.Columns.Count
Range("A1:" & GetColLet(cc) & ActiveSheet.UsedRange.Rows.Count).Select
MsgBox "range selected is " & Selection.Address
End Sub


Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function


Gord Dibben MS Excel MVP

 

[AltaEgo]

If you are trying to select the used range try

ActiveSheet.UsedRange.Select

 

[AltaEgo]

Should also have provided an answer to give you a bullet proof last cell
address:

http://www.beyondtechnology.com/geeks012.shtml


In the demo, replace .Row with .Address

 

[M Skabialka]

Found it:
Private Function ConvertNumberToLetter(ByVal ColumnNumber As Integer) As
String
Dim result As String
result = ""
Do While ColumnNumber > 0
ColumnNumber = ColumnNumber - 1
result = Chr(ColumnNumber Mod 26 + 65) & result
ColumnNumber = CInt(Int(ColumnNumber / 26))
Loop
ConvertNumberToLetter = result
End Function

Given that I know how many
columns are in a spreadsheet - how do I convert

 

[Dave Peterson]

You don't need to use a letter(s) if you don't want to.

with activesheet
.Range"A1",.cells(.UsedRange.Rows.Count, .usedrange.columns.count)).Select
end with

Be aware that .usedrange.rows.count may not correspond to the last row used in
your worksheet.

If you have a new sheet (just for ease of testing) and put data in X991:Z994,
the number of rows in the used range is 4, but it wouldn't be 1:4, it'd be
991:994.

And the same thing with the .usedrange.columns.count

You could use:

Dim LastRow as long
dim LastCol as long

with activesheet.usedrange
lastrow = .rows(.rows.count).row
lastcol = .columns(.columns.count).column
.range("A1",.cells(lastrow,lastcol)).select
end with

 

[M Skabialka]

I am using this on a sheet whose size varies, and need to select everything
to the right and below cell I6. I was using the UsedRange to count how many
columns and rows there were so I could select this range and transpose it
while copying to another sheet. I was able to find code that worked, but am
surprised there isn't a built-in function to do this, or that I can't use a
Column number in a formula.

Be aware that .usedrange.rows.count may not correspond to the last row
used in
your worksheet.

I am not sure what you mean by this - will I get an error and possibly not
include everything I need using UsedRange?

Mich

 

[Dave Peterson]

In VBA, you can use .cells(row,column) and that column parm can be a legal
string or a legal number. But you don't need to use a string--in fact, it's
usually more work to do the conversion.

 

[M Skabialka]

I got errors when I tried using the numeric value - can't recall exactly
what they were now - a couple of days ago..

 

[Dave Peterson]

If you used a valid number, then that wasn't the cause.

Maybe you had a different syntax error in your cells() statement.

 

[Rick Rothstein]

I am using this on a sheet whose size varies, and need to select

everything to the right and below cell I6.

This will select that range..

With Activsheet
Intersect(.UsedRange, .Range("J7", .Cells(.Rows.Count, ..Columns.Count))).Select
End With

Note: I used J7 because you seemed to be saying that I6 should not be included in the selection. If that interpretation is wrong and you wanted to include I6, then just change the "J7" to "I6".

--
Rick (MVP - Excel)

I am using this on a sheet whose size varies, and need to select everything
to the right and below cell I6. I was using the UsedRange to count how many
columns and rows there were so I could select this range and transpose it
while copying to another sheet. I was able to find code that worked, but am
surprised there isn't a built-in function to do this, or that I can't use a
Column number in a formula.

Be aware that .usedrange.rows.count may not correspond to the last row
used in
your worksheet.

I am not sure what you mean by this - will I get an error and possibly not
include everything I need using UsedRange?

Mich

 

[M Skabialka]

Thanks,
That uses a lot less code than I was using!
Mich

I am using this on a sheet whose size varies, and need to select
everything to the right and below cell I6.

This will select that range..

With Activsheet
Intersect(.UsedRange, .Range("J7", .Cells(.Rows.Count,
..Columns.Count))).Select
End With

Note: I used J7 because you seemed to be saying that I6 should not be
included in the selection. If that interpretation is wrong and you wanted to
include I6, then just change the "J7" to "I6".