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
 
Ad

Advertisements

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

Advertisements

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

Maybe you had a different syntax error in your cells() statement.
 
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
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
 
Ad

Advertisements

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top