0

I've a worksheet with various values in only one column like:

A
B
C
D
E
F
...

And I need a write a simple code that will put this all together in one cell (for example D2) and that cell I want to have a value:

A;B;C;D;E;F;...
eli-k
  • 10,898
  • 11
  • 40
  • 44
Michal
  • 443
  • 1
  • 10
  • 25
  • Did you try anything so far? Do you want an Excel based solution or VBA based? Did you search for similar solution in this [site](http://stackoverflow.com/q/11678370/1389394)? Do you see the **related** questions at the right bottom of this page? – bonCodigo Jun 02 '15 at 11:40
  • Excel solution is easy and I used it for a long time, but I can't use it now (the list is changing) and need to upgrade my code. I tried to write it via SQL base, but I felt it is absolutely useless. I wrote only interesing like `recordNumber = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row` – Michal Jun 02 '15 at 11:42

2 Answers2

2

In a simple form, the following VBA macro can do the job (assuming you have values in Excel Worksheet's Column A that you want to concatenate and place a result in the Cell D2):

Sub ConcatenateCells()
Dim i As Integer
Dim count As Integer
Dim s As String
count = Cells(Rows.count, "A").End(xlUp).Row
For i = 1 To count
    s = s & Cells(i, 1) & ";"
Next
Range("D2") = s
End Sub

You can modify it for your purpose. Also, you may exclude the empty cells by adding the condition:

If Cells(i, 1).Value <> "" Then s = s & Cells(i, 1).Value & ";"

Hope this may help. Best regards,

Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
2

If you had Googled, you might stumble upon this link, which is quite funky way of doing it using Excel formula.. 

=CONCATENATE(TRANSPOSE(A1:A6 & ";"))

You just need to make sure that TRANSPOSE function is executed using F9 prior.

However you could always use VBA User Defined Function to get it done too.

PaulFrancis
  • 5,748
  • 1
  • 19
  • 36