Finding last column before blank column

[aa2000]

Hi again

So as part of this seemingly never-ending project, there is a section of code which needs to find the last column before a blank column.

So currently I am using some slightly modified code I found on google, but rather than give just the column address it gives the address of the 1st cell in the column

heres the code:

col = Columns(1).End(xlToRight).Address

So if Column I is blank then col = J1
What should I change so that col just equals J

Thanks

 

[MickG]

This will work up to column Z:-

col = Chr(64 + Columns(1).End(xlToRight).Column)

Mick

 

[aa2000]

Thanks MickG! This does exactly what I needed.

Out of curiosity what is it that limits it to column Z? And how can I change it to work beyond that?

Cheers

 

[MickG]

See the "Chr" function in Vb Help".
Chr(64) is "@" which is one before , Chr(65) which is "A" Chr(66) "B" and so on, If you add you Column Number to Chr(64) you will return the column Letter, as per the code.
You could also do this :-

Col = Split(Columns(1).End(xlToRight).Address, "$")(1)

Mick

 

[aa2000]

Thanks Mick, I looked at the help which meant I could modify it for the other macros.

Cheers!