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

---
Regards,
Norman



"Don Guillett" wrote in message
...
Setting up your test and using below with xl2002 SP2, I got
a1 Don
a2 Fred
a3 Tom

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub
=====
I could NOT replicate.



--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
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(xlCellTypeConst ants)
With ar
.value = Application.Proper(.value)
End With
Next ar
On Error GoTo 0
End Sub

---
Regards,
Norman



"Don Guillett" wrote in message
...
UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"

constituent??
--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
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.

---
Regards,
Norman



"Don Guillett" wrote in message
...
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(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in
message
...
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?