Alternative to concatenate

**Toddneyx** · 12-06-2010, 05:16 PM

Hi,

I'm looking to take a long list of e-mail addresses, probably 100 orso, that will be in Cell A2 downwards, one by one. In non-VB I would've used the concatenate formula to combine them all with a "; " in between, so I could then copy-paste in Outlook.

I know this isn't available in VB, so I was wondering if there's an obvious way to do this? My current longwinded solution is:

Please Login or Register  to view this content.

**Richard Buttrey** · 12-06-2010, 05:37 PM

Not sure what you mean by isn't available in VB, but how about:

Please Login or Register  to view this content.

**Leith Ross** · 12-06-2010, 05:37 PM

Hello Toddneyx,

This code will find the last cell in column "A", and then add a semi-colon to each cell in the range.

Please Login or Register  to view this content.

**snb** · 12-06-2010, 05:46 PM

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**Toddneyx** · 12-06-2010, 06:49 PM

Originally Posted by snb

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

And if my data was in a specific worksheet, would the following work?

Please Login or Register  to view this content.

I get the error Object variable or With block variable not set

**snb** · 12-07-2010, 04:41 AM

Please Login or Register  to view this content.

I hope you realise it's an unnecessary step, as I illustrated in my previous post.

**Toddneyx** · 12-07-2010, 05:07 AM

Sorry I must've missed your point in the previous post, which step is unnecessary?

**snb** · 12-07-2010, 05:19 AM

The defining of a namedrange.

This will do:

Please Login or Register  to view this content.

and if you want to make that more robust:

Please Login or Register  to view this content.

**Toddneyx** · 12-07-2010, 05:38 AM

Ah ok, wasn't sure how to write the reference to another worksheet within that

**Toddneyx** · 12-08-2010, 09:15 AM

I've managed to get it working, partially - now it's just a matter of getting these two functions working under one button.

Please Login or Register  to view this content.

Is my sub PasteData() not "ending" correctly? Because if I assign these two subs to two different buttons, then it works 100%. But if I assign both subs to one button, or both functions under one sub - it fails.