Merging rows....!!
Posted by Dan on February 22, 2001 1:32 AM
I hope someone can help as this is driving me crazy!! Does anyone know of a way of merging a number of rows in the same column into one row other than using the concatenate function through Excel (using this I have to enter each line number and then it will only merge up to 30 lines). Basically, I have a paragraph of text that has been imported into Excel over a number of different lines. I now want to see them just in one.
Thanks!!
dan
Posted by Celia on February 22, 2001 4:09 AM
Didn't the macro that I posted work?
Posted by Danielle on February 22, 2001 6:28 AM
Re: Didn't the macro that I posted work?
Sorry Celia!!! I must have missed it. I've just tried it and it works really well!!
Thanks!
Posted by Danielle on February 22, 2001 6:59 AM
Re: macro.....one more thing!!!
Sorry to be cheeky but do you know of a way to change this macro to make it more of an automatic process. The problem is that there are around 10,000 records that I need to do this to.
Posted by Celia on February 22, 2001 7:22 AM
Re: macro.....one more thing!!!
Dan
Need more info on how each record is separated (e.g. blank row between each set, or next set in next column, or all in one column but fixed number of rows per set, or different sheet for each set, or whatever).
Need info so as to to know how to write the code to select each set one by one.
Also, where are the sets and where do you want to put the concatenated cells?
Celia
Posted by Dan on February 22, 2001 8:31 AM
Re: macro.....one more thing!!!
Celia,
the more I think about this, the more I'm thinking that it isn't possible!! Here goes anyway.....
Basically, I have customer numbers in column A and notes related to each customer in column B. The notes for each customer have been sperated over a number of rows with no spaces in between the rows (not a fixed number either - they range from 20 to 50 or 60!!). The only common thing is that there is always a space of 1 or 2 rows that seperates one set of customer notes from another. Ideally, I would like to take the notes for each customer and put them in the next column (C) so that each set of notes are in 1 line only.
e.g.
A C
1 cust1 notes for cust 1
notes for cust 1
2 cust2 notes for cust 2
notes for cust 2
Does this make sense??
Dan
Posted by Celia on February 22, 2001 3:39 PM
Re: macro.....one more thing!!!
Dan
Try this :-
Sub MergeCells()
Dim startCell As Range, destCell As Range
Dim rng As Range, sourceCell As Range
Set startCell = Range("B2")
Set destCell = Range("C2")
Set rng = Range(startCell, startCell.End(xlDown))
Do
For Each sourceCell In rng
destCell.Value = destCell & " " & sourceCell
Next
Set startCell = startCell.End(xlDown).End(xlDown)
Set rng = Range(startCell, startCell.End(xlDown))
Set destCell = startCell.Offset(0, 1)
Loop While destCell.Row <> 65536
End Sub
I have assumed that the data starts in row 2.
I have not included any code to remove the data from column B nor to delete the resulting blank rows. If you need to do this, you should be able to get the necessary code by using the macro recorder.
Celia
Posted by Dan on February 23, 2001 12:59 AM
Re: macro.....one more thing!!!
Thanks Celia!!!
It worked really well until row 758. Then I got a message saying "Run-time error 13: Type mismatch". It seems to be having problems with the line :
destCell.Value = destCell & " " & sourceCell
Any ideas? Also, it seems to only copy over a max of 21 lines. Is there any way to increase this?
Thank you so much for your help!!!
Dan
Posted by Celia on February 23, 2001 1:41 AM
Can someone else please sort this out (Dave Hawley?)
Sorry, I have to rush off to catch a plane.
Posted by Tim Francis-Wright on February 24, 2001 9:01 PM
Perhaps this will help
If there is an error value in column B, the
macro will generate a type 13 error. To
get it to work by ignoring errors, put the
line
On Error Resume Next
after the Sub MergeCells() line.
If that isn't the problem, perhaps you could
post what's in cells B757 and B758.
--Tim Francis-Wright
