Option Explicit Sub Macro21() ' David McRitchie, misc, 2001-08-06 ' 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