PDA

View Full Version : Macro to combine columns without formulas



decadence
02-01-2016, 04:02 AM
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
02-01-2016, 04:51 AM
You'd better use a macro.

Paul_Hossler
02-01-2016, 10:55 AM
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
02-01-2016, 01:41 PM
cross post http://www.mrexcel.com/forum/excel-questions/918122-macro-combine-columns-without-formulas.html

SamT
02-01-2016, 08:39 PM
Cross Posting (http://www.vbaexpress.com/forum/showthread.php?18537-Cross-Posting&p=137004&viewfull=1#post137004)

decadence
02-02-2016, 03:55 AM
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
02-02-2016, 05:14 AM
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
02-02-2016, 06:50 AM
Complies OK here

As a possibility, go to Tools, References and check


15336

Paul_Hossler
02-02-2016, 06:59 AM
Compiles OK here

As a possibility, go to Tools, References and check


15336

decadence
02-02-2016, 08:32 AM
Hi Paul I don't have version 16.0, I only have version 12.0

SamT
02-02-2016, 10:47 AM
Version number doesn't matter

Paul_Hossler
02-02-2016, 11:33 AM
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