I had an old script on hand that accomplished most of what I think you're after, though it uses a different approach. I modified it and it seems to work with your data. You'll need to update at least a couple values in the code — see comments.
Option Explicit
Sub GSFC_V4()
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks("ConcatTest.xlsx") 'update workbook name
Dim ws As Worksheet
Set ws = wb.Sheets("Sheet1") 'update worksheet name
Dim TextS As String
Dim TextF As String
Dim TextC As String
Dim StartRow As Long
StartRow = 5 'assumes row 5 is the first row containing data to be concatenated
Dim LastRow As Long, lRow1 As Long, lRow2 As Long, lRow3 As Long
LastRowlRow1 = Range("K" & Rows.Count).End(xlUp).Row
lRow2 'assumes= columnRange("L" K& isRows.Count).End(xlUp).Row
lRow3 the= firstRange("M" column& containingRows.Count).End(xlUp).Row data
LastRow to= beWorksheetFunction.Max(lRow1, concatenatedlRow2, lRow3)
Dim CheckCell As Range
Dim ConcatRange As Range
Set ConcatRange = Range(ws.Cells(StartRow, "K"), ws.Cells(LastRow, "K"))
Dim i As Long
i = StartRow
For Each CheckCell In ConcatRange
TextS = ws.Cells(i, "K").Value
TextF = ws.Cells(i, "L").Value
TextC = ws.Cells(i, "M").Value
i = i + 1
If Not CheckCell.Value = "" Then
CheckCell.Offset(0, -3).Value = TextS & TextF & TextC
End If
Next CheckCell
Application.ScreenUpdating = True
End Sub
I had an old script on hand that accomplished most of what I think you're after, though it uses a different approach. I modified it and it seems to work with your data. You'll need to update at least a couple values in the code — see comments.
Option Explicit
Sub GSFC_V4()
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks("ConcatTest.xlsx") 'update workbook name
Dim ws As Worksheet
Set ws = wb.Sheets("Sheet1") 'update worksheet name
Dim TextS As String
Dim TextF As String
Dim TextC As String
Dim StartRow As Long
StartRow = 5 'assumes row 5 is the first row containing data to be concatenated
Dim LastRow As Long
LastRow = Range("K" & Rows.Count).End(xlUp).Row 'assumes column K is the first column containing data to be concatenated
Dim CheckCell As Range
Dim ConcatRange As Range
Set ConcatRange = Range(ws.Cells(StartRow, "K"), ws.Cells(LastRow, "K"))
Dim i As Long
i = StartRow
For Each CheckCell In ConcatRange
TextS = ws.Cells(i, "K").Value
TextF = ws.Cells(i, "L").Value
TextC = ws.Cells(i, "M").Value
i = i + 1
If Not CheckCell.Value = "" Then
CheckCell.Offset(0, -3).Value = TextS & TextF & TextC
End If
Next CheckCell
Application.ScreenUpdating = True
End Sub
I had an old script on hand that accomplished most of what I think you're after, though it uses a different approach. I modified it and it seems to work with your data. You'll need to update at least a couple values in the code — see comments.
Option Explicit
Sub GSFC_V4()
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks("ConcatTest.xlsx") 'update workbook name
Dim ws As Worksheet
Set ws = wb.Sheets("Sheet1") 'update worksheet name
Dim TextS As String
Dim TextF As String
Dim TextC As String
Dim StartRow As Long
StartRow = 5 'assumes row 5 is the first row containing data to be concatenated
Dim LastRow As Long, lRow1 As Long, lRow2 As Long, lRow3 As Long
lRow1 = Range("K" & Rows.Count).End(xlUp).Row
lRow2 = Range("L" & Rows.Count).End(xlUp).Row
lRow3 = Range("M" & Rows.Count).End(xlUp).Row
LastRow = WorksheetFunction.Max(lRow1, lRow2, lRow3)
Dim CheckCell As Range
Dim ConcatRange As Range
Set ConcatRange = Range(ws.Cells(StartRow, "K"), ws.Cells(LastRow, "K"))
Dim i As Long
i = StartRow
For Each CheckCell In ConcatRange
TextS = ws.Cells(i, "K").Value
TextF = ws.Cells(i, "L").Value
TextC = ws.Cells(i, "M").Value
i = i + 1
If Not CheckCell.Value = "" Then
CheckCell.Offset(0, -3).Value = TextS & TextF & TextC
End If
Next CheckCell
Application.ScreenUpdating = True
End Sub
I had an old script on hand that accomplished most of what I think you're after, though it uses a different approach. I modified it and it seems to work with your data. You'll need to update at least a couple values in the code — see comments.
Option Explicit
Sub GSFC_V4()
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks("ConcatTest.xlsx") 'update workbook name
Dim ws As Worksheet
Set ws = wb.Sheets("Sheet1") 'update worksheet name
Dim TextS As String
Dim TextF As String
Dim TextC As String
Dim StartRow As Long
StartRow = 5 'assumes row 5 is the first row containing data to be concatenated
Dim LastRow As Long
LastRow = Range("K" & Rows.Count).End(xlUp).Row 'assumes column K is the first column containing data to be concatenated
Dim CheckCell As Range
Dim ConcatRange As Range
Set ConcatRange = Range(ws.Cells(StartRow, "K"), ws.Cells(LastRow, "K"))
Dim i As Long
i = StartRow
For Each CheckCell In ConcatRange
TextS = ws.Cells(i, "K").Value
TextF = ws.Cells(i, "L").Value
TextC = ws.Cells(i, "M").Value
i = i + 1
If Not CheckCell.Value = "" Then
CheckCell.Offset(0, -3).Value = TextS & TextF & TextC
End If
Next CheckCell
Application.ScreenUpdating = True
End Sub
lang-vb