CODEWITHSUNDEEP
vbaexcel
JessieQuick
JessieQuick

Reputation: 51

How to merge 3 columns to 1 new column with VBA

I'm trying to merge 3 column they all have a text (string) in them

lets say Column A is like this:

  1. Hi " are you from ?

  2. Hi " are you from ?

  3. Hi " are you from ?

    Column B:

  4. Peter

  5. mom

  6. dad

Column C:

  1. DK
  2. JP
  3. GB

I want my out put in column D to be:

  1. Hi Peter" are you from DK?
  2. Hi mom" your are from JP?
  3. Hi dad" your are from GB?

My own code for this so fare is:

Sub MergeColumns()

    Dim LR As Long
    Dim cell As Range, RNG As Range

    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set RNG = Range("A1:A" & LR)

    For Each cell In RNG
           cell.Offset(0, 4) = Left(cell.Value, 2) & Range(B1:B) &   Mid(cell.Value, 4, 13) & Range(C1:C) & Right(cell.Value, 1)
    Next cell

    Range("C:C").Columns.AutoFit

End Sub

I kind of think the bug is at my Range(B1:B) and Range(C1:C) but i have no idea how to insert the column i tried with "Concatenate" but this just messed it up.

Upvotes: 1

Views: 650

Answers (2)

Coolshaikh
Coolshaikh

Reputation: 146

The previous answer is a good solution and you could also do it this way.

Sub MergeColumns()

    Dim LR As Long
    Dim cell As Range, RNG As Range

    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set RNG = Range("A1:A" & LR)

    For Each cell In RNG
        cell.Offset(0, 3) = Left(cell.Value, 2) & " " _
                    & cell.Offset(0, 1) & " " _
                    & Mid(cell.Value, 4, 13) & " " _
                    & cell.Offset(0, 2) & Right(cell.Value, 1)
    Next cell

    Range("C:C").Columns.AutoFit

End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

I would use a pair of Replace functions on the Range.Value2 property.

Option Explicit

Sub wqwewuy()
    Dim rw As Long, lr As Long

    With ActiveSheet
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        For rw = 1 To lr
            .Cells(rw, "D") = Replace(Replace(.Cells(rw, "A").Value2, _
                                        Chr(34), .Cells(rw, "B").Value2 & Chr(34)), _
                                        Chr(63), .Cells(rw, "C").Value2 & Chr(63))
        Next rw
    End With
End Sub

Upvotes: 2

Related Questions