ExcelBanter - Upper case text

600 rows, but how many columns?

If only one or two, use the UPPER function in helper columns.

=UPPER(A1) which is copied down 600 rows.

This must be done for each column and then the helper columns will be copied and
pasted as values in place.

The originals will then be deleted. Whew.......!!

A macro solution which does all rows and columns in a couple of seconds.

Sub Upper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

On Sun, 24 Sep 2006 11:46:01 -0700, ems2993
wrote:

I'm trying to convert an existing spreadsheet with 600 rows of data to upper
case text