Option Explicit Sub Macro21() ' David McRitchie, misc, 20010806 ' http://wwww.mvps.org/dmcritchie/excel/crlf.htm ' look for Chr(10) from bottom Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim i As Long, j As Long, k As Long For i = Cells(Cells.Rows.Count, "A").End(xlUp).Row _ To 2 Step 1 j = Len(Cells(i, 2))  Len(Replace(Cells(i, 2), _ Chr(10), "")) / Len(Chr(10)) If j > 0 Then Rows(i + 1).Resize(j).Insert For k = 0 To j  1 ' column A maybe should be divided up ' instead of copied ?? Cells(i + k + 1, 1) = Cells(i, 1) ' column B Cells(i + k + 1, 2) = Mid(Cells(i + k, 2), _ InStr(1, Cells(i + k, 2), _ Chr(10), vbTextCompare) + 1) Cells(i + k, 2) = Left(Cells(i + k, 2), _ InStr(1, Cells(i + k, 2), _ Chr(10), vbTextCompare)  1) ' column C Cells(i + k + 1, 3) = Mid(Cells(i + k, 3), _ InStr(1, Cells(i + k, 3), _ Chr(10), vbTextCompare) + 1) Cells(i + k, 3) = Left(Cells(i + k, 3), _ InStr(1, Cells(i + k, 3), _ Chr(10), vbTextCompare)  1) Next k End If Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub 

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997  2004, F. David McRitchie, All Rights Reserved