gizmo688
02-02-2012, 09:16 PM
I have several cells with multiple cr lf lines before text. I would like to get rid of these. I am currently using this code. it works when i tell it to remove reoccuring letters, but not when I tell it to remove reoccuring vbcrlf's. Any ideas as to why? For the moment, I am just testing with cell 58, 4. These #'s will be replaced by Q and W once it starts working.
Sub clean()
Dim ModString As String
Dim Holder As String
Dim i As Integer
'For Q = 4 To 6
'For W = 50 To 59
ModString = Sheet17.Cells(58, 4)
For k = 1 To Len(ModString)
Holder = ModString
i = InStr(k, ModString, Chr(13) & Chr(10))
j = InStr(k + 1, ModString, Chr(13) & Chr(10))
If j = i + 1 Then
ModString = Left(Holder, i - 1)
ModString = ModString + Right(Holder, Len(Holder) - i)
Exit For
End If
Sheet17.Cells(58, 4) = ModString
Next k
'Next W
'Next Q
End Sub
Sub clean()
Dim ModString As String
Dim Holder As String
Dim i As Integer
'For Q = 4 To 6
'For W = 50 To 59
ModString = Sheet17.Cells(58, 4)
For k = 1 To Len(ModString)
Holder = ModString
i = InStr(k, ModString, Chr(13) & Chr(10))
j = InStr(k + 1, ModString, Chr(13) & Chr(10))
If j = i + 1 Then
ModString = Left(Holder, i - 1)
ModString = ModString + Right(Holder, Len(Holder) - i)
Exit For
End If
Sheet17.Cells(58, 4) = ModString
Next k
'Next W
'Next Q
End Sub