combine multiple rows in one column

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

 

[Jacob Skaria]

I tried your code with 2 cells A10,A11. But the data finally appears in A11
which is the last cell...?? Could you please check

 

[Shamoun Ilyas]

the data appears in A10 when i run the macro

 

[Jacob Skaria]

Replace 1 with J. Try the below code

Dim J As Integer

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