concat varying number of rows of a single column to 1 cell
Hi
I'm new to vbax.
I would appreciate if any one help me to sort my problem.
I request is to concatenate each cell of one column and have them in a single cell, if there is any repeated values then ignore that cell value
For eg. in the attached xl I have sheet EmailID where number of email id will get populated in column B, i want them all concatenated in one single cell say C2 also any duplicate ids needs to be excluded.
I think I can help you, but I need some more details in order to be sure I understood correctly your request.
1. Do you need to be able to choose which column to be concatenated, or it will always be column B?
2. What type of result are you expecting? For the example given, should the output in cell C2 be "Test1@xxx.comTest2@xxx.comTest3@xxx.comTest4@xxx.comTest5@xxx.comTest6@xxx .com"?
2. Yes exactly same i'm expecting as the email id will get populated with a semicolon once i get this the range c2 has been refered for automail To address recipients.
[VBA]Sub Concatenate()
'
' Concatenate Macro
'
Dim LR As Long, i As Integer
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
i = 3