If you change the line in the second if statement that reads:
Range("B" & RowCount) = Data
To
Range("C" & RowCount) = Data

Then your "answer" will be stored in Column C instead of column B.


"nmpb" wrote:

Thank you for such a quick response. It works.
Can you change it so that the result appears in column C please so it does
not overwrite the data in column B.


"Joel" wrote:

Sub CombineRows()

RowCount = 1
Data = ""
Do While Range("B" & RowCount) < ""
If Data = "" Then
Data = Range("B" & RowCount)
Else
Data = Data & ";" & Range("B" & RowCount)
End If

If Range("A" & (RowCount + 1)) < "" Then
Range("B" & RowCount) = Data
Data = ""
RowCount = RowCount + 1
Else
Rows(RowCount).Delete
End If
Loop
End Sub


"nmpb" wrote:

Hi
I need a program to be able to concate all the rows in B until the cell in
column A is
not blank. would also want a separator between them.
ie row C1 = 000034001570;000034001571;000034001582;00003400158 9
C6 = 000034032303

A B
1 000034001570 000034001570
2 000034001571
3 000034001582
4 000034001589
5 000034001589
6 000034032303 000034032303
7 000034066598 000034066598
8 000034017214 000034017214
9 000034017215
10 000034019302
11 000034019303

The issue is that I have a sheet of over 40,000 rows, also I have noticed
that I have duplicates in the column B, which will have to be excluded.
Its been a while since I've done any programming so any help appreciated