VBA code to concatenate cells from a different worksheet down an entire column

clamont7 · Sep 11, 2018

Good morning,

I have been working on recording a macro for a concatenate function, but I keep getting a "Run-time error '1004': Application-defined or object-defined error". Here's what my code looks like:

Range("G6").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE('List from Sch Cap Graph'!RC[14],"" "",'List from Sch Cap Graph'!RC[16],"" "",'List from Sch Cap Graph'!RC[18],"" "",'List from Sch Cap Graph'!RC[20],"" "",'List from Sch Cap Graph'!RC[22],"" "",'List from Sch Cap Graph'!RC[24],"" "",'List from Sch Cap Graph'!RC[26],"" "",'List from Sch Cap Graph'!RC[28],"" "",'List from Sch Cap Graph'!RC[30],"" "",'Lis" & _
"ch Cap Graph'!RC[32],"" "",'List from Sch Cap Graph'!RC[34],"" "",'List from Sch Cap Graph'!RC[36],"" "",'List from Sch Cap Graph'!RC[38],"" "",'List from Sch Cap Graph'!RC[40],"" "",'List from Sch Cap Graph'!RC[42],"" "",'List from Sch Cap Graph'!RC[44],"" "",'List from Sch Cap Graph'!RC[46],"" "",'List from Sch Cap Graph'!RC[48],"" "",'List from Sch Cap Graph'!RC[" & _
",'List from Sch Cap Graph'!RC[52])"
Range("G6").Select
Selection.AutoFill Destination:=Range("G6:G48")
Range("G6:G48").Select
Range("G2").Select
End Sub


My goal is to Concatenate all cells in a row from column U:BG on sheet "List from Sch Cap Graph" skipping one column each time delimited with a space (Ex. U," ",W," ",Y," ",AA, etc...,BG). Then having that result pasted to G6, G7, G8, etc for however many rows down on sheet "Jeff's List".

The data on "List from Sch Cap Graph" starts in row 6 and goes on for an undefined amount of rows depending on the list generated from a separate function. Any Cells after column BM could be used as a helper cell for the concatenate function, then deleted afterwards.

Thank you in advance for helping me go from programming macros to actually writing code!

Yongle · Sep 13, 2018

Does column U have a value in every row? If not, use a different column to determine last row

Code:

Sub JoinText()
    Dim cel As Range, a As Integer, txt As String, ws As Worksheet, lastRow As Long
    Set ws = Sheets("List from Sch Cap Graph")
    lastRow = ws.Range("[COLOR=#ff0000]U[/COLOR]" & Rows.Count).End(xlUp).Row
    
    For Each cel In ws.Range("U6:U" & lastRow)
        txt = cel.Value
            For a = 2 To 39 Step 2
                txt = txt & " " & cel.Offset(, a).Value
            Next a
        Sheets("Jeff's List").Range("G" & cel.Row).Value = WorksheetFunction.Trim(txt)
    Next cel
End Sub

clamont7 · Sep 17, 2018

Column U does have a value in every row. This code did exactly what I wanted it to. For anyone looking for a concatenate function in VBA, this is great! You can just change the worksheet names and columns. Thank you so much for the help! I'm sure this will help a ton of people!

Yongle · Sep 17, 2018

Thank you for the feedback

VBA code to concatenate cells from a different worksheet down an entire column

clamont7

New Member

Excel Facts

Yongle

Well-known Member

clamont7

New Member

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

VBA code to concatenate cells from a different worksheet down an entire column

clamont7

New Member

Excel Facts

Yongle

Well-known Member

clamont7

New Member

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock