LastColumn used as letter(s) in VBA

[GALLAGHE]

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.UsedRange.Columns.Count).Column

Thanks for any assistance,
Kevin

[Chip]

I just got this to work:

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

[anonymousA]

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.UsedRange.Columns.Count).Column
>
> Thanks for any assistance,
> Kevin
>
>

[Mika]

Hi,

You don=B4t need to convert, use something like:
var =3D ActiveSheet.UsedRange.Address _ReferenceStyle:=3DxlA1)

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

Rg=20
Mika

[Chip]

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)

[kevininstructor@state.or.us]

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" <rigel_58@yahoo.com> wrote in message
news:1108675845.388573.299190@c13g2000cwb.googlegroups.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

[kevininstructor@state.or.us]

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

"anonymousA" <anonymousA@wanadoo.fr> wrote in message
news:4215092c$0$823$8fcfb975@news.wanadoo.fr...
> 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.UsedRange.Columns.Count).Column
> >
> > Thanks for any assistance,
> > Kevin
> >
> >

[kevininstructor@state.or.us]

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

"Chip" <cneuman@gmail.com> wrote in message
news:1108676082.374252.287560@l41g2000cwc.googlegroups.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)
>

[Tom Ogilvy]

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

"kevininstructor@state.or.us" <Kevin.S.Gallagher@state.or.us> wrote in
message news:u9v9BtTFFHA.2156@TK2MSFTNGP10.phx.gbl...
> 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" <rigel_58@yahoo.com> wrote in message
> news:1108675845.388573.299190@c13g2000cwb.googlegroups.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
>
>

[kevininstructor@state.or.us]

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" <twogilvy@msn.com> wrote in message
news:eQ2%23%23YWFFHA.3908@TK2MSFTNGP12.phx.gbl...
> 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
>
> "kevininstructor@state.or.us" <Kevin.S.Gallagher@state.or.us> wrote in
> message news:u9v9BtTFFHA.2156@TK2MSFTNGP10.phx.gbl...
> > 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" <rigel_58@yahoo.com> wrote in message
> > news:1108675845.388573.299190@c13g2000cwb.googlegroups.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
> >
> >
>
>

[JBUK]

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

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

[Tom Ogilvy]

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:

>