Macro - concatenated cells until if finds a blank cell [Closed]

Question

Previous Topic Next Topic

Macro - concatenated cells until if finds a blank cell [Closed]

Report

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: 2758
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 17, 2021

- 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

Concatenate until blank cell excel
Copy non blank cells excel vba ✓ - Forum - Excel
Match blank cell excel ✓ - Forum - Excel
Find and replace blank cells in excel ✓ - Forum - Programming
How to display warning/alert messages if cells are blank in excel vba ✓ - Forum - Excel
Excel - A Macro to copy and paste data in next blank cell - How-To - Excel

1 reply

Ask a question

TrowaD · Answer 1 · 2013-01-15T10:56:57-0500

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

1 reply

Your reply

Subscribe To Our Newsletter!