Microsoft Office Forums - View Single Post - [Solved] Any macro to selectively get rid of soft returns?

macropod · 05-13-2018, 11:04 PM

I suspect you want to do something like:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, j As Long, StrTmp As String
With Selection
  For i = 1 To .Cells.Count
    StrTmp = .Cells(i).Value
    For j = Len(StrTmp) - 1 To 1 Step -1
      If Mid(StrTmp, j, 1) = Chr(10) Then
        If (Mid(StrTmp, j - 1, 1) <> Chr(10)) And (Mid(StrTmp, j + 1, 1) <> Chr(10)) Then
          StrTmp = Left(StrTmp, j - 1) & " " & Right(StrTmp, Len(StrTmp) - j)
        ElseIf (Mid(StrTmp, j + 1, 1) = Chr(10)) And (Mid(StrTmp, j + 2, 1) = Chr(10)) Then
          StrTmp = Left(StrTmp, j - 1) & Right(StrTmp, Len(StrTmp) - j)
        End If
      End If
    Next
    .Cells(i).Value = StrTmp
  Next
End With
Application.ScreenUpdating = True
End Sub