Macro using CLEAN & PROPER on a worksheet.

  • Thread starter Thread starter George
  • Start date Start date
G

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
 
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
 
Back
Top