Trim and Proper

Posted by Rich on November 15, 2001 4:13 AM

My data as it first arrives into excel is untidy i.e
DANnny Milkside___________ (acutal spaces to the right)
i use trim and proper to clean it up ending with:
Dannny Milkside

I have written a macro to perform this for me, but only 1 cell at a time, how can i get the macro to loop through all off the cells in a list - which can differ in numbers each time i do the procedure

Thanks

Richard

Posted by Dank on November 15, 2001 4:23 AM

How about this?

Sub TrimProper()
'Will trim and proper the current selection
Dim rngeToChange As Range, rngeEachCell As Range

On Error Resume Next
Set rngeToChange = Selection
If rngeToChange Is Nothing Then Exit Sub 'No valid selection
On Error GoTo 0

For Each rngeEachCell In rngeToChange.Cells
rngeEachCell = WorksheetFunction.Proper(Trim(rngeEachCell))
Next
End Sub

Hope it helps,
Daniel.

Posted by Richard on November 15, 2001 4:35 AM

worked like a dream,

many thanks

richard