Reputation: 35
Selection.Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
I have a spreadsheet with multiple cells that contain multiple newlines ( the little question mark box symbol). What I'm trying to do is find all the newlines add a new row for each newline found and paste the content after the newline into the new row. I'm new to macros so I tried recording one to try to understand it. Right now the above code is finding the new line and replacing it with a space in the same cell. Not sure how to go about adding the new row?
Upvotes: 3
Views: 7128
Reputation: 96791
This is a partial answer. It is for a single cell
Select a cell containing text with included hard returns and run:
Sub dural()
Dim r As Range, s As String, HR As String
Set r = Selection(1)
v = r.Value
HR = Chr(10)
If InStr(v, HR) = 0 Then Exit Sub
ary = Split(v, HR)
For i = 1 To UBound(ary)
r.Offset(1, 0).EntireRow.Insert
Next i
For i = 0 To UBound(ary)
r.Offset(i, 0).Value = ary(i)
Next i
End Sub
You should embed this in loop(s) to cover all the cells in question.
Upvotes: 2