Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combine multiple rows in one column
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combine multiple rows in one column
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 -- 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combine multiple rows in one column
the data appears in A10 when i run the macro
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combine multiple rows in one column
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 -- If this post helps click Yes --------------- Jacob Skaria "Shamoun Ilyas" wrote: the data appears in A10 when i run the macro |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine information from multiple rows into one | Excel Discussion (Misc queries) | |||
combine contents of multiple rows in one row | Excel Discussion (Misc queries) | |||
Combine Data from Multiple Rows | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) |