unable to remove paragraph-special characters from string values

[RAYLWARD102]

I've loaded a data set from our accounting database, into an array. The accounting database users appear to be accidentally pressing the enter key in the description field, saving the data afterwards. When I dump my array containing all of this information, into an excel sheet, the description's (only some rows, 1 in every 400 records) expands the row height to double it's size, due to the invisible paragraph mark I presume. In excel, there is no way to see these characters, but I did find a handy dandy example for detecting if an unwanted character existed in the value: =IF(CLEAN(A1)=A1,"NA","Needs Cleaning")
Great, so I can identify that a cell has some invisible character resulting in the row height being doubled, but have no way, via VBA to remove such a character.
I've tried everything possible, and these marks wont go away; any idea's?

My trying to remove unwanted characters in a loop across fields containing unwanted characters.

  Eliminate = Chr(13) & Chr(10)
            For y = 2 To 6 'fields 2-6
                v(F(y), x) = Application.WorksheetFunction.Clean(CStr(v(F(y), x)))
                'v(F(y), x) = Replace(v(F(y), x), Chr(182), "")
                'v(F(y), x) = Replace(v(F(y), x), Chr(13), "")
                'v(F(y), x) = Replace(v(F(y), x), vbCrLf, "")
                'v(F(y), x) = Replace(v(F(y), x), ChrW$(244), "")
                'v(F(y), x) = Replace(v(F(y), x), Eliminate, "")
            Next y

as you can see, the application.worksheetfunction.clean can identify the bad character in a cell formula (see above formula example), but cannot be used to produce a clean value (clean by removing paragraph mark)
Pulling my hair out shortly....

 

[JoeMo]

Not certain this will work, but you can try it on a copy of your data. Select the data cells first, then run the macro.

Sub TrimALLMcRitchie()
   'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm
   '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), replacement:=Chr(32), _
     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(21), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   '---------------------------
   Selection.Replace What:=Chr(8), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(9), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

 

[RAYLWARD102]

problem with that approach is I don't plan on dumping to excel sheet; data will be accessed via user form, for printing labels (send it to external application). I only dumped to spreadsheet, to see if I was able to remove invisible characters (for testing).
Where I run into problems is when I send a field containing an unwanted paragraph mark to a label field; screws everything up.

 

[Peter_SSs]

Largely untested as I don't have any sample data or your entire code, but see if you can incorporate something like this into you code to see if it does the job.

Dim RX As Object

Set RX = CreateObject("VBScript.RegExp")
RX.Global = True
RX.Pattern = "(" & Chr(13) & Chr(10) & "?)|" & Chr(182) & "|" & vbCrLf & "|" & ChrW$(244)

For y = 2 To 6
  v(F(y), x) = RX.Replace(v(F(y), x), "")
Next y