Changing concat function to a VBA

[dlo1503]

HI

I am currently writing a code to concatenate data to create warehouse labels. At the minute I am using the concat function however it is throwing up issues when I mail merge to word. The problem is as I use the concat funtion as a continuous code in column M when I mail merge it returns an infinite number of labels as the concat is running the full length of the excel sheet.

I set my concat to only generate information for rows that are TRUE.

I would like to make this a looped VBA code that will only generate info for rows that are TRUE. This would mean there is only data in Column M for cells that are TRUE and not show the concat code in blank rows (if that makes sense). My screenshot shows the concat code still appearing in cell M8 even though there is no data in columns A-L

My current formula is

=IF(A2>0,CONCATENATE($O$3, $P$3, CHAR(10), B2,CHAR(10), L2, CHAR(10),$S$1, D2, CHAR(10), F2, $T$1, $U$1, E2),"")

Please disregard info in columns K and L, these are not needed. The TPO is the purchase order number

Thanks
Dan

 

[JLGWhiz]

Sub t()
Dim c As Range
For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    If c <> "" Then
        c.Offset(, 12) = Range("O3").Value & Range("P3").Value & vbLf & c.Offset(, 1).Value & _
        vbLf & c.Offset(, 11).Value & vbLf & Range("S1").Value & c.Offset(, 3).Value & vbLf & _
        c.Offset(, 5).Value & Range("T1").Value & Range("U1").Value & c.Offset(, 4).Value
    End If
Next
End Sub

 

[dlo1503]

Thank you so much @JLGWhiz that works perfectly. I have had to change my code slightly to put one of the values last now instead of in the middle. Can you help again please? The location of cell L2 has changed in the original concat

=IF(A2>0,CONCATENATE($O$3, $P$3, CHAR(10), B2,CHAR(10), $S$1, D2, CHAR(10), F2, $T$1, $U$1, E2, CHAR(10),L2),"")

 

[JLGWhiz]

Sub t2()
Dim c As Range
For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    If c <> "" Then
        c.Offset(, 12) = Range("O3").Value & Range("P3").Value & vbLf & c.Offset(, 1).Value & _
        vbLf & Range("S1").Value & c.Offset(, 3).Value & vbLf & c.Offset(, 5).Value & Range("T1").Value & _
        Range("U1").Value & c.Offset(, 4).Value & vbLf & c.Offset(, 11)
    End If
Next
End Sub

 

[dlo1503]

Thank you so much @JLGWhiz this has saved me a huge amount of time. Appreciate your assistance

 

[JLGWhiz]

Happy to help,
Regards, JLG