CODEWITHSUNDEEP
vbaexcelexcel-2007
user3801227
user3801227

Reputation: 33

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

Upvotes: 2

Views: 6978

Answers (3)

sabhareesh
sabhareesh

Reputation: 334

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

Upvotes: 1

Thuruv
Thuruv

Reputation: 78

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.

Upvotes: 0

Paresh J
Paresh J

Reputation: 2419

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

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

Upvotes: 0

Related Questions