I have the following code to determine the last column used which returns a
numeric. Is there a method to translate this alpha using VBA e.g. 33 = "AG"

LastColumn =
ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRang e.Columns.Count).Column

Thanks for any assistance,
Kevin

I just got this to work:

lastcolumn = Mid(ActiveSheet.UsedRange.Columns.Address, 7, 1)

Hi,

I would be careful about using the UsedRange method, for this one can be
fooled by any fomatting that has been previously done by the user.
For instance, if you enter a value in a cell just below the end of your
data table and wipe it out later, the UsedRange method will return the
number of the row (as the last one) where you previously entered the
data whereas there's nothing anymore.

So long

GALLAGHE a écrit :
I have the following code to determine the last column used which returns a
numeric. Is there a method to translate this alpha using VBA e.g. 33 = "AG"

LastColumn =
ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRang e.Columns.Count).Column

Thanks for any assistance,
Kevin

Hi,

You don´t need to convert, use something like:
var = ActiveSheet.UsedRange.Address _ReferenceStyle:=xlA1)

and then with the text functions (mid, left, etc) you can extract form
var the column letter you need.

Rg
Mika

I improved upon this because it doesnt account for the last column
being AG (as opposed to Z). So here you go:

lastcolumnname = ActiveSheet.UsedRange.Columns.Address
startofcolumnname = WorksheetFunction.Search(":", lastcolumnname, 4) +
2
endofcolumname = WorksheetFunction.Search("$", lastcolumnname,
startofcolumnname) - 1
lastcolumn = Mid(lastcolumnname, startofcolumnname, endofcolumname + 1
- startofcolumnname)

Mika,

Excellent! One question, I am using version 2002 and had to change the code
to below lose the "_" preciding ReferenceStyle to run.

var = ActiveSheet.UsedRange.Address(ReferenceStyle:=xlA1 )

Any reason why?

Many thanks,
Kevin



"Mika" wrote in message
oups.com...
Hi,

You don´t need to convert, use something like:
var = ActiveSheet.UsedRange.Address _ReferenceStyle:=xlA1)

and then with the text functions (mid, left, etc) you can extract form
var the column letter you need.

Rg
Mika

Thanks for the advise, I will take this under consideration. Again thanks
for your time.

"anonymousA" wrote in message
...
Hi,

I would be careful about using the UsedRange method, for this one can be
fooled by any fomatting that has been previously done by the user.
For instance, if you enter a value in a cell just below the end of your
data table and wipe it out later, the UsedRange method will return the
number of the row (as the last one) where you previously entered the
data whereas there's nothing anymore.

So long

GALLAGHE a écrit :
I have the following code to determine the last column used which
returns a
numeric. Is there a method to translate this alpha using VBA e.g. 33 =
"AG"

LastColumn =

ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRang e.Columns.Count).Column

Thanks for any assistance,
Kevin

Chip,
Many thanks for the assistance and your time!!!
Kevin

"Chip" wrote in message
oups.com...
I improved upon this because it doesnt account for the last column
being AG (as opposed to Z). So here you go:

lastcolumnname = ActiveSheet.UsedRange.Columns.Address
startofcolumnname = WorksheetFunction.Search(":", lastcolumnname, 4) +
2
endofcolumname = WorksheetFunction.Search("$", lastcolumnname,
startofcolumnname) - 1
lastcolumn = Mid(lastcolumnname, startofcolumnname, endofcolumname + 1
- startofcolumnname)

Mika meant it to be one statement continued on a second line:

var = ActiveSheet.UsedRange.Address( _
ReferenceStyle:=xlA1)

would work as well.

Also, I have never seen a need to convert a column number to a letter - are
you sure you need to.
--
Regards,
Tom Ogilvy

" wrote in
message ...
Mika,

Excellent! One question, I am using version 2002 and had to change the
code
to below lose the "_" preciding ReferenceStyle to run.

var = ActiveSheet.UsedRange.Address(ReferenceStyle:=xlA1 )

Any reason why?

Many thanks,
Kevin



"Mika" wrote in message
oups.com...
Hi,

You don´t need to convert, use something like:
var = ActiveSheet.UsedRange.Address _ReferenceStyle:=xlA1)

and then with the text functions (mid, left, etc) you can extract form
var the column letter you need.

Rg
Mika

I should have realized the "_" but don't typically break code on more then
one line beings all developers have 21" dual monitors. Concerning the need
to convert the numeric to a letter, not something I ever needed before but
it's for working with a type library in another programming language.


"Tom Ogilvy" wrote in message
...
Mika meant it to be one statement continued on a second line:

var = ActiveSheet.UsedRange.Address( _
ReferenceStyle:=xlA1)

would work as well.

Also, I have never seen a need to convert a column number to a letter -
are
you sure you need to.
--
Regards,
Tom Ogilvy

" wrote in
message ...
Mika,

Excellent! One question, I am using version 2002 and had to change the
code
to below lose the "_" preciding ReferenceStyle to run.

var = ActiveSheet.UsedRange.Address(ReferenceStyle:=xlA1 )

Any reason why?

Many thanks,
Kevin



"Mika" wrote in message
oups.com...
Hi,

You don´t need to convert, use something like:
var = ActiveSheet.UsedRange.Address _ReferenceStyle:=xlA1)

and then with the text functions (mid, left, etc) you can extract form
var the column letter you need.

Rg
Mika

I'm not a frequent excell/VBA user, but can't you just do this with on
hit with a couple of splits?:

Split(Split(ActiveSheet.UsedRange.Columns.Address, ":")(1), "$")(1

--
JBU
-----------------------------------------------------------------------
JBUK's Profile: http://www.excelforum.com/member.php...fo&userid=3385
View this thread: http://www.excelforum.com/showthread.php?threadid=34657

Are you talking last column for a particular row.

or are all your rows of equal length.

Or are they of unequal length and you need to find the last actual column
that contains a value.

The suggestion was made to use UsedRange, but the purpose of UsedRange is to
reveal the extent of the cells for which Excel is storing detailed
information and has nothing to do whether the cells actually contain data or
not (although it will at least include those).

Perhaps a clarification of what you want will reveal a good answer.

--
Regards,
Tom Ogilvy


"unknown" wrote: