Macro/SUB to merge two columns of text
 

EXCEL 2003: How do I write a VBA subroutine to:
(1) Take each cell in the currently-SELECTED column
(2) Append to its (text) contents the text in the corresponding cell of
the NEXT column
(3) Replace the result in the original cell
(4) On completion, delete the whole of the NEXT column?
All text values need to have leading and trailing blanks TRIMMED before
being concatenated.

This is to cope with the situation where downloaded data contains
information split over two columns that the worksheet needs to have in one,
e.g. company name and branch location.

Macro/SUB to merge two columns of text
 

Sub maketwocolumnsone()
ac = ActiveCell.Column
'line below trims spaces
Columns(ac).Resize(, 2).Replace " ", ""

lr = Cells(Rows.Count, ac).End(xlUp).Row
For i = 2 To lr
Cells(i, ac).Value = Cells(i, ac) & " " & Cells(i, ac + 1)
Next i
Columns(ac + 1).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hershmab" wrote in message
...
EXCEL 2003: How do I write a VBA subroutine to:
(1) Take each cell in the currently-SELECTED column
(2) Append to its (text) contents the text in the corresponding cell of
the NEXT column
(3) Replace the result in the original cell
(4) On completion, delete the whole of the NEXT column?
All text values need to have leading and trailing blanks TRIMMED before
being concatenated.

This is to cope with the situation where downloaded data contains
information split over two columns that the worksheet needs to have in
one,
e.g. company name and branch location.

Macro/SUB to merge two columns of text
 

The following macro should do what you want...

Sub CombineColums()
Dim Cell As Range
Const Delimiter As String = ""
For Each Cell In Selection
Cell.Value = Trim(Cell.Value) & Delimiter & Trim(Cell.Offset(, 1).Value)
Next
Selection.Offset(, 1).EntireColumn.Delete
End Sub

Note: I provided a delimiter constant (the Const statement) that you can set to the text you want between the joined columns' text (that is, set it to a space, or a comma, or a space-comma, or whatever delimiter you want). Right now, I set it to the empty string because your post appears to be asking for no delimiter, but I'm thinking that was just an oversight on your part..

--
Rick (MVP - Excel)


"Hershmab" wrote in message ...
EXCEL 2003: How do I write a VBA subroutine to:
(1) Take each cell in the currently-SELECTED column
(2) Append to its (text) contents the text in the corresponding cell of
the NEXT column
(3) Replace the result in the original cell
(4) On completion, delete the whole of the NEXT column?
All text values need to have leading and trailing blanks TRIMMED before
being concatenated.

This is to cope with the situation where downloaded data contains
information split over two columns that the worksheet needs to have in one,
e.g. company name and branch location.

Macro/SUB to merge two columns of text
 

Columns(ac).Resize(, 2).Replace " ", ""

I'm not sure you want to "trim" the spaces using the above line... what if one (or more) of the cells contained text with multiple words separated by spaces?

--
Rick (MVP - Excel)


"Don Guillett" wrote in message ...
Sub maketwocolumnsone()
ac = ActiveCell.Column
'line below trims spaces
Columns(ac).Resize(, 2).Replace " ", ""

lr = Cells(Rows.Count, ac).End(xlUp).Row
For i = 2 To lr
Cells(i, ac).Value = Cells(i, ac) & " " & Cells(i, ac + 1)
Next i
Columns(ac + 1).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hershmab" wrote in message
...
EXCEL 2003: How do I write a VBA subroutine to:
(1) Take each cell in the currently-SELECTED column
(2) Append to its (text) contents the text in the corresponding cell of
the NEXT column
(3) Replace the result in the original cell
(4) On completion, delete the whole of the NEXT column?
All text values need to have leading and trailing blanks TRIMMED before
being concatenated.

This is to cope with the situation where downloaded data contains
information split over two columns that the worksheet needs to have in
one,
e.g. company name and branch location.