Excel Macro Loop to concatenate columns from proceeding rows into a column in row above

`I have an excel file created by a data file where a memo field is breaking into other rows. I need to take the proceeding rows columns and concatenate them to the first row's column memo field. I need a macro that will go through the file concatenate these proceeding row columns to the first row column and delete the proceeding rows.

Example:(not able to attach an image because I am a new user)

ColumnA Column B Column C Column D

Row ID Date ID Number Description

2        1/21/2010  2010000135        Music too loud in room. 
         Additionally, you have a pending Notice of Charge.
4        1/21/2010  2010000182        Blasting music in your room. 
         Finding notes 
         I explained discplinary process

What I need it to look like:

2        1/21/2010  2010000135        Music too loud in room. Additionally, you have a pending Notice of Charge.
4        1/21/2010  2010000182        Blasting music in your room. Finding notes I explained discplinary process

开发者_如何学JAVAThe memo description line breaks up in various number of rows, though I can distingquish the rows where the memo line breaks up because the Row ID is blank. How can I concatenate column B of multiple rows to column E (Description) throughout the spreadsheet and delete the rows once they have been concatenated into the description field in a macro until it reaches end of file?

---

Asuming an index in 1st column, a date in 2nd column and a text in 3rd column which overflows to 2nd column in next line (meaning that column 1 in break text lines are empty) ...

Sub Beautify()
Dim R As Range, Idx As Long

    Set R = Selection
    Idx = 1

    Do While Idx < R.Rows.Count                           ' count dynamically changes as we delete rows
        If R(Idx + 1, 1) = "" Then                        ' found a break line looking 1 down
            R(Idx, 3) = R(Idx, 3) & " " & R(Idx + 1, 2)   ' append to current
            R(Idx + 1, 1).EntireRow.Delete                ' delete following but do not count up Idx
        Else
            Idx = Idx + 1                                 ' this one is clean, advance
        End If
    Loop
End Sub

select your full list and run the macro ....

before Beautify()
=================
1   01.01.2010  Text 1
2   01.10.2010  Text 1
    Text 2  
3   01.10.2010  Text 1
    Text 2  
    Text 3  
4   01.01.2010  Text 1
5   01.10.2010  Text 1
    Text 2  

after Beautify()
=================
1   01.01.2010  Text 1
2   01.10.2010  Text 1 Text 2
3   01.10.2010  Text 1 Text 2 Text 3
4   01.01.2010  Text 1
5   01.10.2010  Text 1 Text 2

---

The row Id couldn't be blank. The rows have been splitted either by

1) <Alt>+<enter> at the end of each line or 

2) your columns are too narrow.  

For getting rid of the Alt+Enter:

Select the range, press Ctrl+H, hold down the ALT key and
type in "0010" (no quotes) on your numeric keypad. Press
TAB and in the "Replace With" type a space. 
Press "Replace All".