The problem could be that, depending on your text (and OS?), line breaks can either be "\n", "\r", "\r\n", or "\n\r". Your code only looks for "\n", so if your text also has "/r" as part of the line break, then you code leaves these in, and your output text can still appear to have line breaks.
Try this code instead:

This page might help you out : http://dotnetperls.com/string-split
I have a macro that I have been using that does something similar, but uses Regular Expressions:

Using RegEx, it looks for one or both "\r" and "\n" and removes them.
If you just want to clear the empty lines (and not worry about the whitespace), you can just use the last RegEx in the code above.
I haven't fully tested this, but it seems to work well for me. Let me know if you have any trouble.

Jan 27, 2011  • #2

Hi Splat
you're right it does appear to be the line endings that are different between the "Test Macro" option and what's on the clipboard - I did look for "\n\r" but I didn't try it the way you had the Split function, i.e. the {"\n", "\r"}.
I get SQL statements via email - they look ok in Outlook but when pasted into notepad or wherever they have extra lines in between but this looks like it cures that particular issue.
I'll take a look at the RegEx stuff - that's really useful, especially as there are so many pre-tested regex examples out there.
thanks again
Chris

Jan 28, 2011  • #3

@Splat: Thanks for jumping in and helping out.

Feb 4, 2011  • #4