Excel 2007 Concatenate column 1

[Bluejay07]

Hello,

I have data in an excel file that I want concatenated from 2 columns.
I have found several examples that work by creating references.

My problem is that I want to delete the source columns after concatenation.

Example
A[tab][tab][tab]B[tab][tab][tab]C
"ABC"[tab]"DEF"[tab][tab]"ABCDEF"

Delete columns A & B.
Column A should now have "ABCDEF"

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

I think I've figured out one way to accomplish this using paste special.

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("C2:C" & lngLastRow).Value = "=A2 & B2"
Columns("C:C").Select
Selection.Copy
Columns("D:D").Select
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft

If at first you don't succeed, then sky diving wasn't meant for you!

 

[SkipVought]

Hi,

Well you have the formula for concatenation, like =A2&B2.

So after copy/paste down column C...

SELECT Column C

COPY

Right-Click in the selection and select PASTE VALUES.

Proceed to delete columns A & B.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bluejay07]

Thanks for the reply Skip.


If at first you don't succeed, then sky diving wasn't meant for you!