Previous Topic Next Topic

Macro - concatenated cells until if finds a blank cell

Closed
PFMONEY Posts 1 Registration date Monday January 14, 2013 Status Member Last seen January 14, 2013 - Jan 14, 2013 at 11:46 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Jan 15, 2013 at 10:56 AM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jan 15, 2013 at 10:56 AM
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
0