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

possible duplicate of Exel - Concatenate many columns

L42
– L42

2014-09-23 05:39:05 +00:00
Commented Sep 23, 2014 at 5:39 — L42
– L42, Commented Sep 23, 2014 at 5:39

sabhareesh · Accepted Answer · 2014-09-23 06:40:00Z

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

answered Sep 23, 2014 at 6:40

sabhareesh

3341 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Asaf Over a year ago

I would use the same logic with one change... I'

Asaf Over a year ago

I would use the same logic... But I'll prefer to copy the range into an array and get better performence

Paresh J · Accepted Answer · 2014-09-23 05:10:53Z

0

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

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

answered Sep 23, 2014 at 5:10

Paresh J

2,4293 gold badges26 silver badges32 bronze badges

Comments

Thuruv · Accepted Answer · 2014-09-23 05:56:49Z

0

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.

answered Sep 23, 2014 at 5:56

Thuruv

789 bronze badges

Collectives™ on Stack Overflow

Using macro Concatenation of Multiple Columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related