Trim, Clean using Evaluate running into 256 character limit

[capson]

Hello I have large datasets that I run automated processes on that I need to trim and clean and have been using:

Function CleanSheets(arrShtNames As Variant, startRow As Long)
Dim ws As Worksheet
Dim rng As Range
Dim LR As Long, Lc As Long
  
  For Each ws In Worksheets(arrShtNames)
   With ws
   
      Lc = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
      LR = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row  
      Set rng = .Range("A" & startRow).Resize(LR, Lc)

      rng.Replace what:=vbNullChar, Replacement:=vbNullString
      rng.Replace what:="#NULL!", Replacement:=vbNullString
      rng.Replace what:="#VALUE!", Replacement:=""
      
      rng = Evaluate("IF(" & rng.Address & "="""","""",CLEAN(TRIM(" & rng.Address & ")))")
       
   End With
  Next ws
  
End Function

for this. It executes in a few seconds if I use an array and loop through the data it takes minutes

But my recent datasets some cells exceed the character limit and these cells are given the value "#VALUE!" by my function
I want to test for the character limit and if it is exceeded then skip those cells or post back there value

I have tried (and similar things)

rng = Evaluate("IF(Len(" & rng.Address & ")<256,CLEAN(TRIM(" & rng.Address & ")), rng.adress.value)")

but not getting how to postback the value of the cell.

Thanks for any help on this

 

[RoryA]

If you just want the cell values back, it would be:

rng = Evaluate("IF(Len(" & rng.Address & ")<256,CLEAN(TRIM(" & rng.Address & "))," &  rng.address & ")")

 

[capson]

Hello Rory, I tried that and I still get "#VALUE!" the cells exceed the character limit

 

[RoryA]

Then I guess you'd have to return some other text. If you want blanks it would be:

rng = Evaluate("IF(Len(" & rng.Address & ")<256,CLEAN(TRIM(" & rng.Address & ")),"""")")

 

[capson]

Thanks Rory, I guess I have to stop using the Evaluate.

But I just got this and it keeps the speed

Function UltraCleanSheetsList(arrShtNames As Variant, startRow As Long, removeNULL As Boolean)
Dim ws As Worksheet
Dim rng As Range
Dim LR As Long, Lc As Long

  For Each ws In Worksheets(arrShtNames)
   With ws
 
    Lc = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    LR = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Set rng = .Range("A" & startRow).Resize(LR, Lc)
   
      rng.Replace what:=vbNullChar, Replacement:=vbNullString
      rng.Replace what:="#NULL!", Replacement:=vbNullString
      rng.Replace what:="#VALUE!", Replacement:=""
      rng.value = Application.Trim(rng)
      rng.value = Application.Clean(rng)
     
   End With
  Next ws

End Function