Need help creating data strings from a list of values inside of a worksheet.

[xoenix]

This forum has been extremely helpful to me in the past. I'm hoping someone can help me with the following. I'm trying to create a workbook in which a user can dump a list of email addresses in A2:A9999; press a Macro button and have that list translated into a data string in worksheet 2. For example, worksheet 1 has email addresses in A2:A10. After pressing the macro button, these email addresses are displayed as a string in Sheet 2, A1 (test1@email.com; test2@email.com; test3@email.com; etc.). A semi-colon and one space must be used as a separator. I've tried using the concatenate function and was able to generate the string. However, using the concatenate function produced zeros in the data string - carried over from those column entries in sheet 1 which did not have an email address. Anyone have any ideas how I can accomplish this? Any help would be appreciated. Thanks.

 

[MickG]

Try:-

Dim Rng As Range
With Sheets("Sheet1")
    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
Sheets("Sheet2").Range("A1").Value = Join(Application.Transpose(Rng), "; ")