CODEWITHSUNDEEP
vbaexcel
user2166059
user2166059

Reputation: 35

Excel VBA macro that finds newline and adds a new row?

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

Answers (1)

Gary's Student
Gary's Student

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

Related Questions