VBA Syntax for Excel's CLEAN Function

[peterv6]

Does anyone know how to code Excel's CLEAN function within VBA? I've tried numerous different ways, and none have worked.

 

[Norie]

Try something like this.

   Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))

 

[Barrie Davidson]

CleanedVariable = Application.WorksheetFunction.Clean(YourVariableToBeCleaned)

Regards,

 

[peterv6]

Barrie & Norrie, thank you both very much!

 

[Xceller]

Try something like this.

   Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))

Hi everyone - I have 'USB in range("A1"), that is atrophy(')&USB and I run the code above it doesn't work. However, if I put '7 in range("A1"), it works. I have been searching overall the web including Searching for Leading Apostrophes (Microsoft Excel) with this code:


For Each c In Selection If c.PrefixCharacter = "'" Then c.Value = c.Value End If Next c

Nothing seems to work for me
</pre>

 

[shg]

The apostrophe just marks the cell as text. There's no need to remove it, but you can do

With Range("A1")
        .Value = .Formula
End with

 

[Xceller]

Thanks shg. I counted len('USB), it returns 3. I think I will just leave it alone, but it just bugs me visually when I see the (') after I cleaned it.

 

[shg]

You're welcome.

 

[Rick Rothstein]

Barrie & Norrie, thank you both very much!

You might also be interested in seeing the "extended" CleanTrim UDF (user defined function) that I posted here...

Trim all Cells in a Worksheet - VBA

 

[Xceller]

Rick - Tried your UDF. It's awesome! Thank you!