#### Chirag R Raval

##### Member

Dear All Respected Experts & Seekers,

I found 1 CleanTrim function created by Respected by Mr Rick Rothsteian on below link

Above function work 1 cell in one time , but based on Evaluate method as per very knowledgeable reference from Chandoo.org

How can this (Sir Rick Rothstein's ) function apply on whole used range in one run without loop by help of Evaluate?

of course source link above from Excel fox have some successfully modified version available , but its run on single cell in one time with the help of loops.

but there are possible to run that in one run without loop through Evaluate but how?

Chandoo.org source ref for Evaluate

.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))

how to modify below in our current requirement?

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String

How to apply Evaluate approach ?

.Value = .Parent.Evaluate("CleanTrim("& activesheet.usedrange &"),True"))")

but not work.

Can some one can help regarding this? thank you in advance , help always be appreciated.

Regards,

Chirag Raval

I found 1 CleanTrim function created by Respected by Mr Rick Rothsteian on below link

### Trim all Cells in a Worksheet - VBA

Have a question in Excel, Access, Powerpoint, Word or Outlook? Ask http://www.excelfox.com/forum/forum.php

www.excelfox.com

Code:

```
Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
Dim X As Long, CodesToClean As Variant
CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
For X = LBound(CodesToClean) To UBound(CodesToClean)
If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
Next
CleanTrim = WorksheetFunction.Trim(S)
End Function
```

### Eliminating Run-time error '13': while trying to eliminate ASCII 160

I have the following working line of code which TRIMs and CLEANs the data. .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address)) I'm trying to merge the following: SUBSTITUTE(#,CHAR(160),"") into the above line to also "weedout" ASCII 160, but the following...

chandoo.org

How can this (Sir Rick Rothstein's ) function apply on whole used range in one run without loop by help of Evaluate?

of course source link above from Excel fox have some successfully modified version available , but its run on single cell in one time with the help of loops.

but there are possible to run that in one run without loop through Evaluate but how?

Chandoo.org source ref for Evaluate

.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))

how to modify below in our current requirement?

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String

How to apply Evaluate approach ?

.Value = .Parent.Evaluate("CleanTrim("& activesheet.usedrange &"),True"))")

but not work.

Can some one can help regarding this? thank you in advance , help always be appreciated.

Regards,

Chirag Raval

Last edited: