PDA

View Full Version : [SOLVED] VBA to merge data and place - between each data from five columns



estatefinds
04-13-2016, 06:32 AM
I have data in the columns D E F G H and want to be able to select this data and run a macro that would merge these columns so data would look like this


A1-A6-B12-C26-D2



B1-C13-D8-D10-E8



A1-A6-B13-C27-D3



B2-C14-D8-D10-E9



A1-A6-B14-C28-D4




see example in file.
Thank you

PAB
04-13-2016, 07:56 AM
Hi Dennis,

Give this a go...


Sub Mrg_Columns_D_To_H()
' ---------------------------------------------------------------------------------------
' Concatenate columns "D:H".
' ---------------------------------------------------------------------------------------
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Range("J1:J" & Range("D" & Rows.Count).End(xlUp).Row).Formula = _
"=D1&""-""&E1&""-""&F1&""-""&G1&""-""&H1"
Columns("J").EntireColumn.AutoFit
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

I hope this helps!

Paul_Hossler
04-13-2016, 08:13 AM
simple, brute force



Sub MergeCells()
Dim iRow As Long

For iRow = 1 To Range("D1").CurrentRegion.Rows.Count
Cells(iRow, 10).Value = Cells(iRow, 4).Value & "-" & _
Cells(iRow, 5).Value & "-" & _
Cells(iRow, 6).Value & "-" & _
Cells(iRow, 7).Value & "-" & _
Cells(iRow, 8).Value
Next
End Sub

PAB
04-13-2016, 08:22 AM
Hi Dennis,

You might want to clear the contents of column "J" first before you run the program.
You may also just want the values as opposed to the formulas to show in column "J".
You can just adapted the code accordingly to do either or both of these.

EDIT: I have just noticed that some of the cells in columns "D:H" have an extra space at the end of the values, this is giving a space for those values when they are output to column "J", you could also incorporate the TRIM function into the code as well.

estatefinds
04-13-2016, 12:33 PM
Great job!!! thanks again I was struggling with merges before.
Thanks again it works great!!!
I tried both and work perfectly!!!

PAB
04-14-2016, 12:47 AM
Great job!!! thanks again I was struggling with merges before.
Thanks again, it works great!!!
I tried both and work perfectly!!!

You're welcome, thanks for the feedback.

Don't forget to include the extra lines of code so it excludes errors.