Search code examples
vbaexcelexcel-2007

Using macro Concatenation of Multiple Columns

please anybody help in concatenating cells using Excel 2007 macro. I have columns A to E. I want to concatenate all the Columns in column F. Please note that I don't know the exact number of rows in the all the Columns, but macro should stop concatenating when there are no values in the said columns. Sample:

A       B           C          D     E           F
O      ABC         DEF        GHI    E       OABCDEFGHIE

O      JKL         MNO        PQR    E       OJKLMNOPQRE

O      STU                   VWXYZ   E       OVWXYZE
Solution

  • May be you can try with the following code:

    Sub concat()
Dim i As Integer
For i = 1 To ActiveSheet.UsedRange.Rows.Count
For j = 1 To 1
If (Cells(i, j).Value <> "") And (Cells(i, j + 1).Value <> "") And (Cells(i, j + 2).Value <> "") Then
Sheets("Sheet1").Range("D" & i).Value = Cells(i, j).Value + Cells(i, j + 1).Value + Cells(i, j + 2).Value
Else
Sheets("Sheet1").Range("D" & i).Value = "Empty cell found"
End If
Next j
Next i
End Sub

    It may look long but i hope you ll get some idea...