Search code examples
excelvba

RegExp inserting a line break when array prints to cell?

I am iterating through cells in column D to clean up comments data that are in wrapped cells. I just have it assign a line break whenever a date pops up in a wall of comment text to organize it so each date = new line break in cell.

it works fine, but for some reason the RegExp is adding in a blank line break when printing the array? I double click in the cell and theres a blank line break as you can see above. I don't want that, just the beginning of text to be up there.

enter image description here

Code:

Sub CommentFormatting()
    Dim i As Long
    Dim oSht As Worksheet
    Dim lastRow As Long
    Dim objRegExp As Object
    Dim dataRange As Variant
    Dim outputData() As Variant
    
    Set objRegExp = CreateObject("vbscript.regexp")
    Set oSht = Sheets("Master")
    
    lastRow = oSht.Cells(oSht.Rows.Count, "D").End(xlUp).Row
    dataRange = oSht.Range("D5:D" & lastRow).Value
    
    ReDim outputData(1 To UBound(dataRange, 1), 1 To 1)
    
    With objRegExp
        .Global = True
        .Pattern = "(\d{1,2}/\d{1,2}/\d{2,4})"
        
        For i = 1 To UBound(dataRange, 1)
            If Not IsError(dataRange(i, 1)) And Not IsEmpty(dataRange(i, 1)) Then
                Dim sTxt As String
                sTxt = Trim(dataRange(i, 1))
                If .Test(sTxt) Then
                    If Left(sTxt, 1) = vbLf Then
                        sTxt = Mid(sTxt, 2) ' Remove the first line break
                    End If
                    If Right(sTxt, 1) = vbLf Then
                        sTxt = Left(sTxt, Len(sTxt) - 1) ' Remove the last line break
                    End If
                    outputData(i, 1) = .Replace(sTxt, vbLf & "$1") ' Add line break before the pattern
                Else
                    outputData(i, 1) = dataRange(i, 1)
                End If
            End If
        Next i
    End With
    
    oSht.Range("D5:D" & lastRow).Value = outputData

End Sub
Solution
    • .Replace(sTxt, vbLf & "$1") should be run before removing extra line break.
    
Sub CommentFormatting()
    Dim i As Long
    Dim oSht As Worksheet
    Dim lastRow As Long
    Dim objRegExp As Object
    Dim dataRange As Variant
    Dim outputData() As Variant
    
    Set objRegExp = CreateObject("vbscript.regexp")
    Set oSht = Sheets("Master")
    
    lastRow = oSht.Cells(oSht.Rows.Count, "D").End(xlUp).Row
    dataRange = oSht.Range("D5:D" & lastRow).Value
    
    ReDim outputData(1 To UBound(dataRange, 1), 1 To 1)
    
    With objRegExp
        .Global = True
        .Pattern = "(\d{1,2}/\d{1,2}/\d{2,4})"
        
        For i = 1 To UBound(dataRange, 1)
            If Not IsError(dataRange(i, 1)) And Not IsEmpty(dataRange(i, 1)) Then
                Dim sTxt As String
                sTxt = Trim(dataRange(i, 1))
                If .Test(sTxt) Then
                    sTxt = .Replace(sTxt, vbLf & "$1") ' ** Add line break before the pattern
                    If Left(sTxt, 1) = vbLf Then
                        sTxt = Mid(sTxt, 2) ' Remove the first line break
                    End If
                    If Right(sTxt, 1) = vbLf Then
                        sTxt = Left(sTxt, Len(sTxt) - 1) ' Remove the last line break
                    End If
                    outputData(i, 1) = sTxt ' **
                Else
                    outputData(i, 1) = dataRange(i, 1)
                End If
            End If
        Next i
    End With
    
    oSht.Range("D5:D" & lastRow).Value = outputData

End Sub