Formula is too long

  • Thread starter Thread starter Henry
  • Start date Start date
H

Henry

Hello,

I have a large Excel 2000 spreadsheet that has a column that gives a
html description of each product. I am trying to run the following
macro to remove unwanted CR and LF, etc.

ActiveSheet.Cells.Replace Chr(160) , ""
ActiveSheet.Cells.Replace Chr(13) , ""
ActiveSheet.Cells.Replace Chr(10) , ""

This seems to work until the script comes to a description that is
very long. At that point I get "Formula is too long".

Is there a way around this issue?

Thanks for any help!
Henry
 
Henry,

Select the range which you want the cr's, lf's, and crlf's removed from and run this

Sub ClearCRLF()
For Each Cell In Selection
temp = Cell.Text
For i = 1 To Len(temp)
a = Mid(temp, i, 1)
If a = vbCrLf Or a = vbCr Or a = vbLf Then
temp = Left(temp, i - 1) & Right(temp, Len(temp) - i - 1)
End If
Next
Cell.Value = temp
Next
End Sub

Dan E
 
Back
Top