Hi Stefi,

Thanks for the code. I have been extremely busy, so just had time to try it.

It gave me an error when I ran it, which I resolved by taking out Option
Explicit.

Now it gives me a syntax error when it gets to:

Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace
- 1)
Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace
+ 1, 256)

I'm not sure how to resolve this.... any help would be greatly appreciated.
--
Thanks!

Dee


"Stefi" wrote:

Hi dee,
I hope this is the sub you need:

Sub SepAltEnt()
Range("A2").Select
Do While Not IsEmpty(ActiveCell)
firstrow = ActiveCell.Row
secondrow = firstrow + 1
nextrow = secondrow
AltEntPlace = 0
On Error Resume Next
AltEntPlace = WorksheetFunction.Search(Chr(10), ActiveCell)
On Error GoTo 0
If AltEntPlace 0 Then
Rows(firstrow).Select
Selection.Copy
Rows(secondrow).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace
- 1)
Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace
+ 1, 256)
AltEntPlace = 0
On Error Resume Next
AltEntPlace = WorksheetFunction.Search(Chr(10), Range("B" &
firstrow))
On Error GoTo 0
If AltEntPlace 0 Then
Range("B" & firstrow) = Left(Range("B" & firstrow),
AltEntPlace - 1)
Range("B" & secondrow) = Mid(Range("B" & secondrow),
AltEntPlace + 1, 256)
End If
nextrow = secondrow + 1
End If
Range("A" & nextrow).Select
Loop
End Sub

Regards,
Stefi

€ždee€� ezt Ã*rta:

Hi,

Another challenge:

I may have, ideally, something like:
Mfg Part Number Qty Price
abc 111222 50 .25
xyz 333554 50 .25

etc. These items may be exactly the same, but are made by different mfg, so
have different part number.

Challenge:

I receive data as follows:

Mfg Part Number Qty Price
abc 111222 50 .25
xyz 333554

where the mfg has been entered in one cell, using Alt Enter and where the
part number has also been entered in one cell using Alt Enter

I need to separate these onto separate rows, but need to copy the qty,
price, etc. onto these lines as well.

I'm fine with adding some columns, using text to columns to divide the data,
but have no idea how to then grab the surround data and repeat it.

Is there any way to do this?
Thanks


--
Thanks!

Dee