I have a list of hundreds of cells, and want to combine them, and thus was looking for an automated solution rather than manual entry.
Any ideas?
Thanks!
Row\Col | A | B | C |
1 | Tom1 | Dick1 | Arries1 |
2 | Tom2 | Dick2 | Arries2 |
3 | Tom3 | Dick3 | Arries3 |
4 | Tom4 | Dick4 | Arries4 |
5 | Tom5 | Dick5 | Arries5 |
6 | Tom6 | Dick6 | Arries6 |
7 | Tom7 | Dick7 | Arries7 |
Aris |
Row\Col | A | B | C | D |
1 | Tom1 | Dick1 | Arries1 | Tom1 ; Dick1 ; Arries1 |
2 | Tom2 | Dick2 | Arries2 | Tom2 ; Dick2 ; Arries2 |
3 | Tom3 | Dick3 | Arries3 | Tom3 ; Dick3 ; Arries3 |
4 | Tom4 | Dick4 | Arries4 | Tom4 ; Dick4 ; Arries4 |
5 | Tom5 | Dick5 | Arries5 | Tom5 ; Dick5 ; Arries5 |
6 | Tom6 | Dick6 | Arries6 | Tom6 ; Dick6 ; Arries6 |
7 | Tom7 | Dick7 | Arries7 | Tom7 ; Dick7 ; Arries7 |
Aris |
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] arisConcatenating()
[color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=lightgreen]'Sheet Info[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
[color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") [color=lightgreen]'Output Range[/color]
[color=lightgreen]'Input Ranges[/color]
[color=blue]Dim[/color] rngA [color=blue]As[/color] Range: [color=blue]Set[/color] rngA = wks.Range("A1:A" & lr & "")
[color=blue]Dim[/color] rngB [color=blue]As[/color] Range: [color=blue]Set[/color] rngB = wks.Range("B1:B" & lr & "")
[color=blue]Dim[/color] rngC [color=blue]As[/color] Range: [color=blue]Set[/color] rngC = wks.Range("C1:C" & lr & "")
[color=lightgreen]'Conctente with Evaluate[/color]
[color=blue]Let[/color] rngD.Value = Evaluate("" & rngA.Address & "" & "&"" ; ""&" & " " & rngB.Address & "" & "&"" ; ""&" & " " & rngC.Address & "")
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'arisConcatenating[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] ArrisArmyTrayConcatenating3()
[color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=darkgreen]'Sheet Info[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
[color=blue]Dim[/color] r [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Rows in Sheet[/color]
[color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") 'Output Range
rngD.Clear [color=darkgreen]'Just so I know the conctnating lines work!![/color]
[color=blue]Dim[/color] Evalstr [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'String argument for Evaluate "One Liner[/color]
[color=darkgreen]' Build string for Evalute "one liner"[/color]
[color=blue]Dim[/color] c [color=blue]As[/color] [color=blue]Long[/color], lc [color=blue]As[/color] Long: [color=blue]Let[/color] lc = 3 [color=darkgreen]'Column, and last column[/color]
[color=blue]For[/color] c = 1 [color=blue]To[/color] lc - 1 [color=blue]Step[/color] 1
[color=blue]Let[/color] Evalstr = Evalstr & "" & wks.Range(wks.Cells(1, c), wks.Cells(lr, c)).Address & "" & "&"" ; ""&" [color=darkgreen]'Concatenate cell values with ; inbetween[/color]
[color=blue]Next[/color] c
[color=blue]Let[/color] Evalstr = Evalstr & "" & wks.Range(wks.Cells(1, lc), wks.Cells(lr, lc)).Address & "" [color=darkgreen]'Concatenate last row without any ;[/color]
[color=darkgreen]'Concatenate with Evaluate[/color]
[color=blue]Let[/color] rngD.Value = Evaluate(Evalstr)
[color=blue]End[/color] [color=blue]Sub[/color] [color=darkgreen]'ArrisArmyTrayConcatenating3[/color]
For example:
Cell A1: Robert
Cell A2: John
Cell A3: Will
Cell B1 (combine the 3 texts automatically, separated by semicolon): Robert;John;Will
Thank you.