Using macro Concatenation of Multiple Columns

Question

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

Answer 1

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...

Answer 2

You can simply use excel formula CONCATENATE, check the below line:

Use this formula in column F =CONCATENATE(A1,B1,C1,D1,E1)

Answer 3

Set a column to put the formula which is in next to the most significant cell. .

=Concatenate($A1:$somecell1)

Here you need not to worry about the exact cell nums.