How do I change a spreadsheet from all caps to "Proper"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet containing data to make mailing labels. The whole thing
was entered in caps. I need to change it to the Proper style ( the first
letter is capitalized and the rest lower case). How can I do this?
 
from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
..Value = Application.Proper(.Value)
End With
End Sub
 
Hi Don,

I think that your sub will overwrite data if the SpecialCells range
comprises more than one area.

The sub needs to loop through the SpecialCells range's constituent cells.
 
UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"
 
Hi Don,

On a blank worksheet,
In A1 enter: DON
In A2: enter FRED
In A3 enter TOM

Now run your sub.

What do you get?

I get DON in all three cells!

As I said you need to loop, either through each area or each cell. Adapting
your sub:

Sub makeproper2()
Dim ar As Range

On Error Resume Next
For Each ar In Activesheet.UsedRange.SpecialCells(xlCellTypeConstants)
With ar
.value = Application.Proper(.value)
End With
Next ar
On Error GoTo 0
End Sub
 
Setting up your test and using below with xl2002 SP2, I got
a1 Don
a2 Fred
a3 Tom

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
..Value = Application.Proper(.Value)
End With
End Sub
=====
I could NOT replicate.
 
Hi Don,
I could NOT replicate
Click to expand...

Because I erroneously gave the test cells as A1. A2 and A3 instead of A1, A3
and A5. Try testing again using these cells.

As another example, I entered the following data into a blank sheet:

DON
123 HIGH STREET
ANYTOWN
<<==Blank row
FRED
345 OTHER STREET
ANOTHER TOWN
<<==Blank row
TOM
678 YET ANOTHER STREET
YET ANOTHER TOWN

After running your sub I get:

Don
123 High Street
Anytown
<<==Blank row
Don
Don
Don
<<==Blank row
Don
123 High Street
Anytown
 
Back
Top