ready to use code

[Richiejjj]

Hi there,

Hoping that someone can help me:

I have a lot of data that i import into excel in csv format, which is untidy. I run the trim and proper functions on the cells.

Is there a easy to write (or use) function that i could assign a button too, which would do this for me.

Thanks

Richard

 

[Damon Ostrander]

Hi Richard,

I don't know if this is exactly what you want, but here is a macro that will Trim and Proper the contents of all cells on the active worksheet:

Sub TrimProper()
' Trims and uppercases the contents of all cells on the
' active worksheet
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
Cell.Value = UCase(Trim(Cell.Value))
Next Cell
End Sub

 

[Richiejjj]

Thanks Damon,

How would i apply this to the selected range of cells.

thanks again

rich

 

[Damon Ostrander]

Hi again Richard,

To apply this to the selected cells, the code should be:

Sub TrimProper()
' Trims and uppercases the contents of all selected cells on the
' active worksheet
Dim Cell As Range
For Each Cell In Selection
Cell.Value = UCase(Trim(Cell.Value))
Next Cell
End Sub

 

[Richiejjj]

HI

this was a long time ago, but i have lost some data!

this puts the items as uppercase, not in sentence case - i have tried amending the code by changing ucase to proper but i get a compile error

any ideas please?

thanks
rich

 

[Andrew Poulsom]

There is no Proper function in VBA, so you need to use the worksheet function, eg:

Cell.Value = WorksheetFunction.Proper(Trim(Cell.Value))

 

[SydneyGeek]

Just went through the Help. There is no Proper function in VBA, so you have to use the Excel version:

Sub TrimProper() 
' Trims and uppercases the contents of all selected cells on the 
' active worksheet 
Dim Cell As Range 
For Each Cell In Selection 
  Cell.Value = Application.Proper(Trim(Cell.Value)) 
Next Cell 
End Sub

HTH
Denis

 

[Richiejjj]

great

many thanks!