Excel VBA - Concatenate Module for Multiple Lines

[Reqless]

Hi all. I am new to excel VBA and I am trying to create a module that will concatenate multiple lines.

Here is an example of data from the excel workbook:

Once the macro is executed, I would like the result to look like this:

Any information would greatly appreciated. Thank you in advanced to anyone willing to contribute.

 

[My Aswer Is This]

Try this:

Sub Combine_Values()
'Modified 3/11/2019 1:24:47 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    Cells(i, 1).Value = Cells(i, 1).Value & " " & Cells(i, 2).Value & " " & Cells(i, 3).Value
Next
Application.ScreenUpdating = True
End Sub

 

[Michael M]

If you have a long list, this may be quicker

Sub MM1()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Value = Evaluate(.Address & "& "" "" & " & .Offset(, 1).Address & " & "" "" &  " & .Offset(, 2).Address)
        .Offset(, 1).Clear
        .Offset(, 2).Clear
    End With
End Sub

 

[Reqless]

Wow, that worked perfect. Thank you!

I have one more problem that I am hoping to find a solution to. Sorry for not posting within the original question!

It is similar to the original question, but different. For this example, the module will concatenate all data from columns B, C & D beginning with cells B2, C2, and D2 and ending with cells B200, C200, and D200. :

 

[Michael M]

Same question really, just the ranges change !!

Sub MM1()
    With Range("B2:B200")
        .Value = Evaluate(.Address & "& "" "" & " & .Offset(, 1).Address & " & "" "" &  " & .Offset(, 2).Address)
        .Offset(, 1).Clear
        .Offset(, 2).Clear
    End With
End Sub

 

[Reqless]

Michael - this worked flawlessly. I really appreciate your help, thank you!

 

[Michael M]

Glad to help....so what do you now do with 2 blank columns ??

 

[Reqless]

I leave it blank or I could remove the column, I guess it technically doesn't make a difference. Once the macro is ran (along side some other macros) the .csv file is uploaded to some software that arranges the data based on the column. When the software hits a blank column, it will proceed forward until it reaches a column containing data. At least that is my understanding of how the software analyzes the .csv file!

 

[Michael M]

Ok, but if you need to remove the blank cols, use...

Sub MM1()
    With Range("B2:B200")
        .Value = Evaluate(.Address & "& "" "" & " & .Offset(, 1).Address & " & "" "" &  " & .Offset(, 2).Address)
        .Offset(, 1).Clear
        .Offset(, 2).Clear
    End With
Range("C:D").EntireColumn.Delete
End Sub

 

[Reqless]

Okay great. Appreciate that. This will be good to have just in case.

Is it possible to combine multiple macros to execute as one process in line order?