How to i capitalize all words in an existing worksheet?

[Guest]

I have a worksheet that contains approximately 2500 names. At this time all
names in in lowercase. I need to get them all into UPPERCASE. I know
there's got to be an easier way to do that rather then retyping each name.

 

[Guest]

It depends how the names are arranged with in the worksheet but for a column
of names (for example) this would work

Sub caps()
Dim myRange As Range
Set myRange = Range("A1:A1000") ' Change to suit
For Each c In myRange
c.Value = UCase(c.Value)
Next
End Sub

right click the sheet tab, view code and paste it in.

Mike

 

[JW]

Going by your post title, I'm assuming that you want EVERY word in the
worksheet converted to uppercase, correct? If so, you can edit Mike's
code to handle the used range within the worksheet.
Sub caps()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c.Value = UCase(c.Value)
Next
End Sub

 

[Gord Dibben]

I'll add my usual caveat about the posted macro.

It will wipe out any formulas in the used range and change them to values.

If any chance of formulas in the usedrange this revision will ignore them.

Sub caps()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then
c.Value = UCase(c.Value)
End If
Next
End Sub


Gord Dibben MS Excel MVP