Need VBA to combine cells in a column

[skyport]

I have data in multiple cells in column E. I need to join them all together in cell K1. Please help.

 

[Myconservator]

You need to provide more information.
How many cells in column E have data?
What are the cell addresses?
How do you want them to be combined, e.g. spaces in between, dash in between...?
Where do you want the output to be located?

 

[Peter_SSs]

See if this helps. Adjust the red joiner as required.

Sub JoinThem()
  Range("K1").Value = Join(Application.Transpose(Range("E1", Range("E" & Rows.Count).End(xlUp)).Value), ", ")
End Sub

 

[skyport]

Peter, Great to see you here again. You previously resolved one for me no one else could. I confess to being a novice and not aware of what I should do with your suggestion on adjustment of ", " however, I am currently getting a runtime error 13. If it helps, the maximum cells in Column E should be 350

 

[Peter_SSs]

Peter, Great to see you here again.

Thanks

I confess to being a novice and not aware of what I should do with your suggestion on adjustment of ", "

This relates to Myconservator's "How do you want them to be combined, e.g. spaces in between, dash in between"
If you have "cat" in one cell and "dog" in the next, do you want K1 to be
"cat, dog" (this is what my code currently does)
"cat dog"
"cat - dog"
etc
Whatever you want between the entries goes between the quote marks in the red part of my code.

.. however, I am currently getting a runtime error 13.

Can you give us anything more to go on? What sort of data do you have in column E? I've just run the code without error with 350 rows of text entries consisting of 1 to 5 words each.

 

[skyport]

To Peter and myConservator: Peter, Great to see you here again. You solved a big one for me a while back that no one else could. I confess to being a novice and not knowing what I need to do with your suggestion about the ", ". If it is of help, the max number of cells in column E should be 350. ( Still enjoying those awesome Australian birds)

MyConservator: The output should be column K cell 1. No special spacing, location- E1-350

 

[skyport]

Sorry for confusion. My first reply did not show up so I reposted, now they both appear.

Peter, Column E would contain data in cells that would be the result of a concatenate of other cells and would include a blank space at the end of each and for this reason I am thinking no need for a bridge.

EXAMPLE:

Column B has generic data telling a story from a certain source
Column F Has the word "Page" in each cell
Column G Has a ":" on each cell
Column E Concatenates F,G," " B " " to result in something that appears like below:

Page 8: The birds of Australia have an admirer. ( with a built in space at the end to separate from next cell entry)

What we now need is to join those cells in Column E to look as below:

Page 1: The duck quacks. Page 2: The geese fly. Page 3: Birds are wonderful. etc etc.

 

[Peter_SSs]

It seems then that you have something ball-park like this?
My code produced what you see in K1.
If you set up another sheet with just this simple data, does my code work for you on that sheet?

If so, copy your real worksheet and then reduce the number of rows to, say, 20 or some other number and see if you can either get it to work for that smaller data or find out which row(s) of data might be causing the error.

Incidentally, I tried copying these two rows down 350 rows & the code still did not error (& produced a VERY long string in K1 )

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1										Page 1:The duck quacks. Page 8:The birds of Australia have an admirer.
2	The duck quacks.			Page 1:The duck quacks.	Page 1	:
3	The birds of Australia have an admirer.			Page 8:The birds of Australia have an admirer.	Page 8	:
Join cells

 

[skyport]

Peter, would it make a difference if this has to be run on excel 2000, which is the case here.

 

[skyport]

Peter, Tried new worksheet idea and still same error code. Again I question if it has to do with being excel 2000