# Macro - concatenated cells until if finds a blank cell

[Closed]
Posts
1
Registration date
Monday January 14, 2013
Status
Member
Last seen
January 14, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

Im trying to concatenated cells with text until it finds a cell with no text then it stops current continate then continues with a new one.
Searched cells vary in quanity

a [result a&b&c&d]
b
c
d
"space"
e [result e&f&&g&h&i&j&k]
f
g
h
i
j
k
"space

Thanks

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Pfmoney,

See if the folowing code works for you.
```Sub test()

Dim lRow, fRow, a, b, x, y, z As Integer

fRow = Range("A" & Rows.Count).End(xlUp).Row
x = 0
z = -1
Do
x = x + 1
lRow = Range("A" & x).End(xlDown).Row
y = lRow - x + 1
a = y
z = z + 1
Do
z = z + 1
If Range("B" & x).Value <> vbNullString Then Range("B" & x).Value = Range("B" & x).Value & "&"
Range("B" & x).Value = Range("B" & x).Value & Range("A" & z).Value
a = a - 1
Loop Until a = 0
b = y + 1
x = x + b - 1
Loop Until x > fRow
End Sub```

Sorry for the many variables, I just added nameless counters until the desired result showed up.

NOTE: I assumed that "Space" in your example means empty cell.

Best regards,
Trowa