Macro to combine columns without formulas

[decadence]

Good Morning all,

I need a macro to combine any columns I select into one column, so if I chose columns A,B,C,D then all data from these columns will be merged into column A with a space, or if I selected Columns D to T then all data from columns D to T would be merged into column D with a space,

Each column must keep the original data on the same row and the columns originally selected are deleted after merging.
Each Column will have varied data with different number of rows
Headers are to be included with this merge
I am using Excel 2007
(the dots are only used to break up the words into columns)
Example

......A........ B........ C........ D......... E
1 Hi.........How......Are......You......Hello
2 ........... How
3 ....................... Are
4 .................................. You

Merged to this

...............A....................B..........C.......D.......E....
1 Hi How Are You..........Hello
2 How
3 Are
4 You

Any help is appreciated,

Thanks in advance

[snb]

You'd better use a macro.

[Paul_Hossler]

Maybe something like this

I made it so you don't require a contiguous selected range

In the attachment you can select all of A : D to combine into A, as well as just M and O (hold the control key down and select M and then O) to combine into M

Option Explicit
Sub CombineColumns()
    Dim rUsed As Range, rArea As Range, rCell As Range, rFirst As Range, rColumn As Range
    
    If Not TypeOf Selection Is Range Then Exit Sub

    Set rUsed = Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)

    If rUsed Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set rFirst = rUsed.Columns(1)

    For Each rArea In rUsed.Areas
        For Each rColumn In rArea.Columns
            If rColumn.Address <> rFirst.Address Then
                For Each rCell In rColumn.Cells
                    If Len(Trim(rCell.Value)) > 0 Then
                        rFirst.Cells(rCell.Row, 1) = rFirst.Cells(rCell.Row, 1) & " " & Trim(rCell.Value)
                    End If
                Next
                
                rColumn.ClearContents
            End If
        Next
    Next
    Application.ScreenUpdating = False

End Sub

[p45cal]

cross post http://www.mrexcel.com/forum/excel-q...-formulas.html

[SamT]

Cross Posting

[decadence]

My apologies for not referencing a cross post, but I wasn't sure if anyone would answer here and I got a reply on another forum

[decadence]

I have tried the code posted above but it errors on the word Trim - If Len(Trim(rCell.Value)) > 0 Then
Error is Compile Error: Wrong number of arguments or invalid property assignment

[Paul_Hossler]

Complies OK here

As a possibility, go to Tools, References and check


Capture.JPG

[Paul_Hossler]

Compiles OK here

As a possibility, go to Tools, References and check


Capture.JPG

[decadence]

Hi Paul I don't have version 16.0, I only have version 12.0

[SamT]

Version number doesn't matter

[Paul_Hossler]

Is OK -- just go by the title, but Len and Trim are part of VBA ..... unless you've defined a function with the same name and then Excel might get confused