Change case for all text in cell range

  • Thread starter Thread starter R. Lehr
  • Start date Start date
R

R. Lehr

I'm trying to change all the names in existing cells (in a contiguous range)
from standard case to all upper case (e.g: A1 = Joe Smith, A2 = Jane Doe,
etc. to: A1 = JOE SMITH, A2 = JANE DOE, etc.). I need the new upper case
values to be in the original cells.

Using "=UPPER(A1:BB82) forces the values in that range to change but the new
upper case values are in a different cell range.

Any ideas?

Thanks in advance.
RLL ~ 1/27/2004
 
Borrowing code from Dave Peterson on truncating Left, this will make
everything in A1:BB86 uppercase.

Sub UCaseMe()
Application.ScreenUpdating = False
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = [A1:BB82]
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub
Else
For Each myCell In myRng.Cells
myCell.Value = UCase(myCell.Value)
Next myCell
End If
Application.ScreenUpdating = True
End Sub

Hope that helps.

-Bob
 
Thanks -

Works great! Much appreciated. [And thanks to Dave Peterson on his
truncating Left part of code.]

I thought Excel had a function that could be slightly tweaked. But this
macro (sub-routine) works great.

~RLL
 
Back
Top