ready to use code

Richiejjj

Board Regular
Joined
Feb 18, 2002
Messages
237
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
There is no Proper function in VBA, so you need to use the worksheet function, eg:

Cell.Value = WorksheetFunction.Proper(Trim(Cell.Value))
 
Upvote 0
Just went through the Help. There is no Proper function in VBA, so you have to use the Excel version:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top