Remove blank line in a string

Question

I want to remove blank lines in a string as follows:

"First section

Second section

Third section"

I display a rolling index on every content slide so as you click through the slides, the index highlights the section you are in. I don't want to display subsections, so I tried to replace section names starting with "-" with "", but that means I have blank lines. So now I want to remove the blank lines.

I tried:

IIF statements but replacing with "" doesn't remove a blank line
Regular expressions, another link suggested the following pattern would work: @"^\s+$[\r\n]*" but the @ throws up an error and it doesn't work in any case

I tried something like the below:

Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")

With RE
    .Multiline = True
    .Global = True  
    resultString = .Replace(subjectString, "\s\n", string.empty)
    MsgBox resultString
End With

Another potential solution I found on stackoverflow.

Dim xArr() as string
xArr = Split(TextBox1.Value, vbCrLf)
TextBox1.Value = ""

for i = 0 to Ubound(xArr)
    If Trim(xArr(i)) <> "" Then
        TextBox1.value = TextBox1.value & xArr(i) & vbCrLf
    End If
Next

I think [this](https://stackoverflow.com/questions/10024402/how-to-remove-line-break-in-string) might help your problem. — Damian, Mar 15 '19 at 12:26

score 1 · Accepted Answer · answered Mar 15 '19 at 12:44

Looks like your RegEx code is actually intended for VB.Net rather than VBA, the code below replaces n blank lines with 1 in VBA.

Dim RE As Object: Set RE = CreateObject("VBScript.RegExp")

With RE
    .MultiLine = True
    .Global = True
    .Pattern = "(\r\n)+"

    resultString = .Replace(subjectString, vbCrLf)

    MsgBox resultString
End With

Of course if you only ever have 2 blank lines you can simply:

resultString = replace$(subjectString, vbcrlf & vbcrlf, vbcrlf)

Brilliant! Thanks Alex K - really appreciate your help – Richard Wallace Mar 15 '19 at 13:17 — Richard Wallace, Mar 15 '19 at 13:17

score 1 · Answer 2 · answered Mar 18 '21 at 20:23

I know this is old but here's a regex Public Function i made to help. Theres probably a better way but this was simple for me and worked.

'=================================================================================='
Public Function RegExReplace(TextContent As String, SearchEx As String, Optional ReplaceEx As String = "", Optional _
                    EmptyLines As Boolean = False, Optional TrimLines As Boolean = True) As String
    Dim regEx As Object, strOutput As String
    Set regEx = CreateObject("vbscript.regexp")
    With regEx: .Global = True: .IgnoreCase = False: .MultiLine = True: .Pattern = SearchEx: End With
    TextContent = regEx.Replace(TextContent, ReplaceEx)
    If EmptyLines = False Then TextContent = RegExReplace(TextContent, "\r\n\r\n", "", True, False)
    If TrimLines = True Then TextContent = Trim(TextContent)
    
    RegExReplace = TextContent: Set regEx = Nothing
End Function
'=================================================================================='

score 1 · Answer 3 · answered Mar 19 '21 at 13:56

If for whatever reason you'd prefer to avoid using RegEx (working on a Mac where VBScript isn't available for example), here's a purely VB approach:

Sub Test()
    Call TakeOutTheEmpties(ActiveWindow.Selection.ShapeRange(1))
End Sub
Sub TakeOutTheEmpties(oSh As Shape)

    Dim oPara As TextRange
    Dim x As Long
    
    If oSh.HasTextFrame Then
        If oSh.TextFrame.HasText Then
            For x = oSh.TextFrame.TextRange.Paragraphs.Count To 1 Step -1
                Set oPara = oSh.TextFrame.TextRange.Paragraphs(x)
                If oPara.Text = vbCr Then
                    oPara.Delete
                End If
            Next
        End If
    End If

End Sub

Remove blank line in a string

3 Answers3