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

D

dumbfounded

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?
 
D

Don Guillett

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
 
N

Norman Jones

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.
 
D

Don Guillett

UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"
 
N

Norman Jones

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
 
D

Don Guillett

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.
 
N

Norman Jones

Hi Don,
I could NOT replicate

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top