Maybe something like:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myArea As Range
Dim myRow As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Selection

Application.DisplayAlerts = False
For Each myArea In myRng.Areas
If myArea.Columns.Count 1 Then
For Each myRow In myArea.Rows
myStr = ""
For Each myCell In myRow.Cells
If myCell.Text < "" Then
myStr = myStr & " " & myCell.Text
End If
Next myCell
myRow.Merge across:=True
myRow.Cells(1).Value = Mid(myStr, 2)
Next myRow
End If
Next myArea
Application.DisplayAlerts = True

End Sub

======
But I'd think twice about doing this. Usually keeping each field in a separate
column makes life much easier. And merged cells cause trouble with
sorting/copy|pasting, too.

Select your range (A1:E99???) and run the macro.

cbrd wrote:

Dont know why the other post didnt post any message but....

I have Columns setup like below. I wish for the columns C, D, E to be
meged into one, but keeping the data and putting a space in between. I
wish to do this with a macro/button instead of using pasting a formula
over and over.

I Have:
A B C D E <------Columns
Doe | 123 Main | Reed | PA | 15222
and more follow....

I wish to get this result:

A B C <-------Columns
Doe | 123 Main | Reed, PA 15222
would also need to insert a comma when merging after column C

--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=500406

--

Dave Peterson