Combining Columns A, B,C via VBA Code

[Dokat]

Hi,

I am trying to write a VBA code to combine Columns A+B+C.

Below is the code i am using but receiving Run Time Error 1004 Application Defined or Object Defined error. Can someone please help me with this error.

Sub Merge()
Dim R1 As Range
Dim R2 As Range
Dim R3 As Range
Set R1 = Worksheets("Source Data").Range("A2:A" & LastRow)
Set R2 = Worksheets("Source Data").Range("B2:B" & LastRow)
Set R3 = Worksheets("Source Data").Range("C2:C" & LastRow)
Sheets("Source Data").Range("AG2:AG" & LastRow) = "R1" & "R2" & "R3"

End Sub

Thanks

 

[chirayu]

code is incorrect. use below

Sub Merge()
Dim Rng As Range
Dim SubRng As Range
Dim LastRow As String

With Worksheets("Source Data")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = .Range("AG2:AG" & LastRow)
    For Each SubRng In Rng
        SubRng = Range("A" & SubRng.Row) & Range("B" & SubRng.Row) & Range("C" & SubRng.Row)
    Next SubRng
End With

End Sub

 

[Stevie]

Hi Dokat,
personally it looks like a job for the =CONCAT() formula, but if you are set on VBA, try this:

Sub specificConcat()
Dim row As Double
Dim lastrow As Double
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("Source Data")
With ws
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
End With
For row = 2 To lastrow
    ws.Range("AG" & row) = ws.Range("A" & row).Text & ws.Range("B" & row).Text & ws.Range("C" & row).Text
Next
Application.ScreenUpdating = True
End Sub

It will run from row 2 (from your description I assume this is correct) to the last row populated in column A.

Stevie

If this was helpful, please click 'Like!' below.

 

[Dokat]

Thank you both