Concatenate Multiple Rows

[Shamoun Ilyas]

I imported a table of data in excel. A common problem faced is that
the data spreads over multiple rows in one column. What i need to do
is then concatenate a few rows. Obviously there are over 300,000 rows
of data, so it will take me forever.

I have found a macro that does for me.

Sub Combine()
Dim J As Integer

If Selection.Cells.Count > 1 Then
For J = 2 To Selection.Cells.Count
Selection.Cells(J).Value = _
Selection.Cells(1).Value & " " & _
Selection.Cells(J).Value
Selection.Cells(1).Clear
Next J
End If
End Sub

To use this macro, you select the cells you want to concatenate and
then run the macro. The contents of all the cells are combined into
the first cell in the selection, then whatever is in the other cells
is cleared.

However what i want to do is that The contents of all the cells are
combined into the LAST (not first) cell in the selection, then
whatever is in the other cells is cleared.

Can someone please suggest to me an alteration in this macro or a new
macro that will achieve the desired result. Thank you.

 

[Pete_UK]

Why don't you just cut and paste those combined values from the first
cell to the last cell after the macro has run? In the macro you can
insert a line after the Next J statement to do this.

By the way, I think you will need to change the first statement to:

Dim J As Double

if you want to deal with up to 300,000 rows. Also, I think you have a
line in the macro that has been missed out, to allocate the value of
cell J to cell 1 and then clear cell J, not cell 1.

Hope this helps.

Pete

 

[Shamoun Ilyas]

I found this VBA on the Internet. I have no VBA skills. Can you help
me with this? How do i add a line to cut and paste in the macro?
Thanks for your assistance.

 

[Pete_UK]

I see that you have posted this request several times, and that you
have received a reply in one of the other groups. Please do not multi-
post.

Pete