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.

Dim J As Integer

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Shamoun Ilyas" wrote:

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.

thank for trying. but this change does not work.

Sub Combine()
Dim J As Integer
If Selection.Cells.Count 1 Then
combcont = ""
For J = 1 To Selection.Cells.Count
combcont = combcont & Selection.Cells(J).Value & " "
Selection.Cells(J).Clear
Next J
Selection.Cells(J - 1).Value = Trim(combcont)
End If
End Sub

Regards,
Stefi


€žShamoun Ilyas€� ezt Ã*rta:

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.

Thank you Stefi. Your help is much appreciated.

You are welcome! Thanks for the feedback!
Stefi

€žShamoun Ilyas€� ezt Ã*rta:

Thank you Stefi. Your help is much appreciated.