Macro - concatenated cells until if finds a blank cell Closed

Question

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

Configuration: Windows 7 / Internet Explorer 8.0

TrowaD · Answer

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

Macro - concatenated cells until if finds a blank cell

1 response

Similar discussions

Subscribe To Our Newsletter!