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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 - 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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
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