Hello everyone. I have a piece of code that I am trying to simplify. I want to Trim off any leading/trailing spaces and then convert all text to Proper. I want to apply this to the entire sheet. I do not want to have to select a section in order to run the code.
This is what I have so far:
This is what I have so far:
Code:
[Sub ToProperCase() For Each cell In ActiveSheet.UsedRange.Cells
cell.Value = WorksheetFunction.Proper(cell.Value)
Next
Dim c As Range, rng As Range
Set rng = Intersect(Selection, Selection.Parent.UsedRange)
If rng Is Nothing Then
MsgBox "No cells with values!"
Exit Sub
End If
For Each c In rng
If Not IsError(c) Then
c.Value = MEGACLEAN(c)
End If
Next c
End Sub
Function MEGACLEAN(varVal As Variant)
Dim NewVal As Variant
If IsMissing(varVal) Then Exit Function
NewVal = Trim(varVal) 'remove spaces
NewVal = Application.WorksheetFunction.Clean(NewVal) 'remove most unwanted characters
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(127), "") 'remove ASCII#127
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(160), "") 'remove ASCII#160
MEGACLEAN = NewVal
End Function]