VBA for Text/Cells merge

[Ron]

With Excel 2002 I need some help please to VBA code the following

A B C D
1 bbb
2 ccc
3 kkk
4 yyy

Merge text cells B1 & B2;
B3 & B4..... etc.......

The merged results should be displayed in A1; A2; etc

Col B varies on a daily basis but is rarely less that 1500 cells.

There are no blank cells in the B col range.

TIA

R.

 

[Guest]

You could do it with formulas, just put this in A1 and copy down, then Copy >
PasteSpecial > Values, and delete the blanks...

=IF(ISODD(ROW(B1)),B1&OFFSET(B1,1,0),"")

Vaya con Dios,
Chuck, CABGx3

 

[Ron]

Chuck,

Entered the formula in A1 and copied down but get #NAME? in each A
cell.

More help/clarifcation needed please.

R.

 

[Guest]

Sorry, forgot to mention you should make sure you have the Analysis ToolPak
installed..........

Tools > Add-Ins > check the Analysis toolPak > OK

Vaya con Dios,
Chuck, CABGx3

 

[Ron]

Chuck,

That did it.... great & thank you.

In the formula

=IF(ISODD(ROW(B1)),B1&OFFSET(B1,1,0),"")

where would I add a "CHAR(10)" to operate between rows 1 & 2, 3 & 4
etc

R.

 

[Ron]

Chuck,

Please disregard my earlier post re the CHAR(10) insert

I figured it out.

=IF(ISODD(ROW(B1)),B1&CHAR(10)&OFFSET(B1,1,0),"")

Again thank you very much for your help

R.

 

[CLR]

You're welcome Ron, and it's good to see you figured out how to edit it
yourself.

Vaya con Dios,
Chuck, CABGx3