Previous Topic Next Topic

Macro - concatenated cells until if finds a blank cell

[Closed]
Report
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,

Please assist.
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

1 reply

Answer 1 / 1
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
Ask a question