Uppercase in a whole sheet
 

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


--
Manu Palao
------------------------------------------------------------------------
Manu Palao's Profile: http://www.excelforum.com/member.php...o&userid=16205
View this thread: http://www.excelforum.com/showthread...hreadid=314309

try

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

--
Don Guillett
SalesAid Software

"Manu Palao" wrote in message
...

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


--
Manu Palao
------------------------------------------------------------------------
Manu Palao's Profile:
http://www.excelforum.com/member.php...o&userid=16205
View this thread: http://www.excelforum.com/showthread...hreadid=314309

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(She et1!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

--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Manu Palao" wrote in message
...

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


--
Manu Palao
------------------------------------------------------------------------
Manu Palao's Profile:
http://www.excelforum.com/member.php...o&userid=16205
View this thread: http://www.excelforum.com/showthread...hreadid=314309

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


--
Don Guillett
SalesAid Software

"Manu Palao" wrote in message
...

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


--
Manu Palao
------------------------------------------------------------------------
Manu Palao's Profile:
http://www.excelforum.com/member.php...o&userid=16205
View this thread: http://www.excelforum.com/showthread...hreadid=314309

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

On Wed, 17 Nov 2004 06:32:11 -0600, Manu Palao
wrote:


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