formatting data
 

How to change data by column or comlete spreadsheet from lower case to upper
case by column.

You can use a formula in a Helper column: =UPPER(A1). Then use Edit/Copy and
Edit/Paste Special,Values to convert those formulas to their values.

Another solution is John Walkenbach's Power Utility Pack, available for
purchase at www.j-walk.com

On Sun, 10 Apr 2005 08:47:02 -0700, smithrdsr
wrote:

How to change data by column or comlete spreadsheet from lower case to upper
case by column.

Sub UppercaseAll()
Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants,
xlTextValues)
Rng.Value = UCase(Rng.Text)
Next Rng
End Sub

"smithrdsr" wrote:

How to change data by column or comlete spreadsheet from lower case to upper
case by column.

Manually using a helper column and the UPPER Function.

In B1 enter =UPPER(A1)

Double-click on the fill handle of B1 to copy down.

VBA Macro.........

Select range to change or for complete sheet hit CTRL + A(twice in 2003) then
run the macro.

Sub optUpper_Click()
'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 Excel MVP

On Sun, 10 Apr 2005 08:47:02 -0700, smithrdsr
wrote:

How to change data by column or comlete spreadsheet from lower case to upper
case by column.

Put in say, B1: =UPPER(A1)
Copy down
Copy col B and paste special as Values to overwrite col A
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"smithrdsr" wrote in message
...
How to change data by column or comlete spreadsheet from lower case to
upper
case by column.