Merge four columns into one cell.

[Livin404]

Greetings,
I'm trying to merge data in four columns ("F:I"). This will begin at Row 2 and go down to the last row in column F that has data. I got a basic VBA where I can get one row to merge. I just need it to include all rows that has data in Column F beginning on Row 2. Finally I need to Merge cells F across to I. Here is the formula that I managed to get one row to cooperate.

Sub Merge()
    Dim lr As Long, i As Long
    lr = Range("F" & Rows.Count).End(xlDown).Row
        For i = 2 To lr
      Range("F" & i) = Range("F" & i).Text & Range("G" & i).Text & Range("H" & i).Text & Range("I" & i).Text
        End
    Next i
   End Sub

Below is an example of a row columns ("F:I"). The end result would be EB175010500002AXXX. Once they are combine I simply need to merge the cells across but begin on Row 1.

Thank you,

 

[Saurabhj]

Hi, Please confirm the output expected if the sheet has below data:

06Feb2022.xlsm
	E	F	G	H	I	J
1		Column F	Column G	Column H	Column I
2		F1	G1	H1	I1
3		F2	G2	H2	I2
4		F3	G3	H3	I3
5
6
7
8
9
Sheet1

 

[Livin404]

Hi, Please confirm the output expected if the sheet has below data:

06Feb2022.xlsm
	E	F	G	H	I	J
1		Column F	Column G	Column H	Column I
2		F1	G1	H1	I1
3		F2	G2	H2	I2
4		F3	G3	H3	I3
5
6
7
8
9
Sheet1

I am trying to use a VBA not a formula bar. In this case the result for Column F2 would be F1G1H1I1. Then those rows would be merged into 1. The same thing would occur for all subsequent rows down to the last row where data is in column F.

Thank you,

 

[Livin404]

Greetings,
I'm trying to merge data in four columns ("F:I"). This will begin at Row 2 and go down to the last row in column F that has data. I got a basic VBA where I can get one row to merge. I just need it to include all rows that has data in Column F beginning on Row 2. Finally I need to Merge cells F across to I. Here is the formula that I managed to get one row to cooperate.

Sub Merge()
    Dim lr As Long, i As Long
    lr = Range("F" & Rows.Count).End(xlDown).Row
        For i = 2 To lr
      Range("F" & i) = Range("F" & i).Text & Range("G" & i).Text & Range("H" & i).Text & Range("I" & i).Text
        End
    Next i
   End Sub

Below is an example of a row columns ("F:I"). The end result would be EB175010500002AXXX. Once they are combine I simply need to merge the cells across but begin on Row 1.

View attachment 57104

Thank you,

I was able to sort it out for the most part I think. I am going with this Macro

Sub Concat_Columns()
Dim WS As Worksheet
Set WS = ActiveSheet
'find last row
LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Dim result As String
For i = 2 To LastRow
    For j = 6 To 9
        If WS.Cells(i, j) <> "" Then
            If result <> "" Then
                result = result & WS.Cells(i, j).Text
            Else
                result = result & WS.Cells(i, j).Text
            End If
        End If
    Next j
    WS.Cells(i, 6) = result
    result = ""
Next i

End Sub

It does concat those four columns now I just need to delete the contents for columns 8:9 then merge 7:9

Thank you

 

[Saurabhj]

Hi,

To merge further (2nd row of each column) and to delete columns 7 to 9, you can use below code:

    With WS
        .Cells(2, 6) = .Cells(2, 6) & .Cells(2, 7) & .Cells(2, 8) & .Cells(2, 9)
        .Range("G:I").Delete
    End With

 

[Livin404]

Hi,

To merge further (2nd row of each column) and to delete columns 7 to 9, you can use below code:

    With WS
        .Cells(2, 6) = .Cells(2, 6) & .Cells(2, 7) & .Cells(2, 8) & .Cells(2, 9)
        .Range("G:I").Delete
    End With

Thank you very much indeed.