Support Request: Trim Cell Function for more than 255 Characters

[DrHacker]

Experts, i have this Macro code used for Clean / Trim selected cells, but it shows me an error if the length its more than 255 Characters. There is a way to adjust the code do paste the full Clean Trim string if its more than that quantity (255+)?

Sub CleanTrimCells_Evaluate()

    Dim Area As Range

If Selection.Cells.Count = 1 Then

Set rng = Selection

Else

Set rng = Selection.SpecialCells(xlCellTypeConstants)

End If

For Each Area In rng.Areas
Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
Next Area

End Sub

Thanks in advance

 

[Fluff]

How about

Sub DrHacker()
   Dim Cl As Range, Rng As Range
   
   Set Rng = Selection.SpecialCells(xlConstants)
   With Application
      For Each Cl In Rng
         Cl.Value = .Clean(.Trim(Cl.Value))
      Next Cl
   End With
End Sub

 

[DrHacker]

Nice , Works flawless

How about

Sub DrHacker()
   Dim Cl As Range, Rng As Range
  
   Set Rng = Selection.SpecialCells(xlConstants)
   With Application
      For Each Cl In Rng
         Cl.Value = .Clean(.Trim(Cl.Value))
      Next Cl
   End With
End Sub

ce,

 

[Fluff]

You're welcome & thanks for the feedback.