Re: Strip carriage return, add row

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
news:OWVxGqQSFHA.508@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <adin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:765FE073-2277-480F-8E61-B37F2A423FFE@xxxxxxxxxxxxxxxx
> > I've got a *** 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
> >
> >
> >
> >
> >
>
>


.

Quantcast