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

M

#### 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

G

#### 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

A

#### AltaEgo

If you are trying to select the used range try

ActiveSheet.UsedRange.Select

M

#### 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

D

#### 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

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

#### 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
I am not sure what you mean by this - will I get an error and possibly not
include everything I need using UsedRange?

Mich

D

#### 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

#### 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..

D

#### Dave Peterson

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

R

#### 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)

M Skabialka said:
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
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

#### M Skabialka

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

Rick Rothstein said:
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".