Macro using CLEAN & PROPER on a worksheet.

[George]

Good Morning,

I have a worksheet that has multiple columns of Cut & Paste
information...Some of the info is in All Caps and addtionally formated with
returns the make the text appear broken up...Can anyone help me with a macro
that runs whenever a button is clicked to run CLEAN & PROPER on columns "B,
G, L, M, N". The number rows in each column varies but always begins at row
3.

Thanks in adavance for your assistance,
Respectfully,
George

 

[Per Jessen]

Good Evening George,

Try this:

Sub CleanProper()
Dim cell As Range

Set RngA = Range("B3", Range("B" & Rows.Count).End(xlUp))
Set RngB = Range("G3", Range("G" & Rows.Count).End(xlUp))
Set RngC = Range("L3", Range("N" & Rows.Count).End(xlUp))

For Each cell In RngA
cell = WorksheetFunction.Proper(cell.Text)
cell = WorksheetFunction.Clean(cell.Text)
Next
For Each cell In RngB
cell = WorksheetFunction.Proper(cell.Text)
cell = WorksheetFunction.Clean(cell.Text)
Next
For Each cell In RngC
cell = WorksheetFunction.Proper(cell.Text)
cell = WorksheetFunction.Clean(cell.Text)
Next
End Sub

Regards,
Per