Uppercase in a whole sheet

[Manu Palao]

Hi
does anybody know if there is a way to convert all the cells of a shee
into their uppercase version without doing it individually?
thanks!

 

[Don Guillett]

try

Sub makeupper()
For Each c In ActiveSheet.UsedRange
c.Value = UCase(c)
Next
End Sub

 

[Max]

One way to try ..

Assume the source data is in Sheet1, A1:E100

In Sheet2
-----------
Put in A1:

=IF(Sheet1!A1="","",IF(ISTEXT(Sheet1!A1),UPPER(Sheet1!A1),Sheet1!A1))

Copy across to E1, fill down to E100 to cover the same range as in Sheet1

Select Sheet2 and kill the formulas with an in-place:
Copy > paste special > values > OK

 

[Don Guillett]

On second thought. If you have formulas use this instead

Sub makeupper()
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then c.Value = UCase(c)
Next
End Sub

 

[Gord Dibben]

Manu

VBA macro OK for you?

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 Excel MVP