Bob,

Once again - you are the macro maestro!

If I need to add columns to the sheet, how do I add them in the macro?

Thanks again!



"Bob Phillips" wrote:

Here's an example of such code


Sub FormatData()
Dim cLastRow As Long
Dim i As Long, j As Long
Dim cLines As Long
Dim aryItems

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
aryItems = Split(Cells(i, "A").Value, Chr(10))
cLines = LBound(aryItems) + UBound(aryItems)
If cLines 1 Then
Cells(i + 1, "A").Resize(cLines).EntireRow.Insert
For j = UBound(aryItems) To LBound(aryItems) Step -1
Cells(i + j, "A").Value = aryItems(j)
Cells(i + j, "B").Value = Cells(i, "B").Value
Next j
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
I don't think you can do this with a formula. You would probably need a
fairly involved macro.

You would need to count the number of line breaks in each cell in column
A,
add that many rows below each cell, split the string, remove the line
breaks, and copy the column B information into the new rows. Not
difficult,
but quite tedious.

--

Vasant

"adin" wrote in message
...
I've got a sheet with 2 columns - column A has multiple values in one
cell
that are separated by carriage returns, and column B next to it has only
one
value per cell.

I'd like to not only strip out these carriage returns, but also (the fun
part) add rows for each value and maintain the reference to the column
next
to it.

Example:

A1 looks like this:

Milk
Eggs
Juice

B1 looks like this:

Food

I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2
should
contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and
C2
should say "Food". You get the idea.

Thanks,

Adin