convert text in excel to uppercase

E

elaine

Is it possible to convert all text in a workbook or on a spreadsheet to all
uppercase?
 
H

Hayeso

In VBA Editor

Sub MakeUpper()
Dim MySht As Worksheet, MyCell As Range
For Each MySht In ThisWorkbook.Sheets
For Each MyCell In MySht.UsedRange.Cells
MyCell = UCase(MyCell)
Next
Next
End Sub
 
D

David McRitchie

There is no such example for this on Chip's page, but you
probably want to change the
MyCell = UCase(MyCell)
to
MyCell.formula = UCase(MyCell.formula)
so you don't wipe out formulas.

My own solution would be "Back to Kindergarten" in
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
which should be considerably, and ignores formulas and
empty cells. Your use of UsedRange will eliminate the
vast ocean of empty cells, but not those within the used range.
 
G

Gord Dibben

Elaine

A warning if you use this MakeUpper macro.

All formulas will be converted to values.

May not be a desired result.

An alternative......

Sub Upper_All_Sheets()
'David McRitchie
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As String, ws As Object, Cell As Range
x = MsgBox("Use CANCEL to abort changing all constant " _
& "cells to uppercase", vbOKCancel)
If x = vbCancel Then Exit Sub
For Each ws In ActiveWorkbook.Sheets
On Error Resume Next 'In case no cells in selection
ws.Activate
For Each Cell In Cells.SpecialCells(xlConstants, xlTextValues)
Cell.Value = UCase(Cell.Value)
Next
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP
 
G

Gord Dibben

Sorry about that David

In my posting I changed your "Kindergarten" sub to "Upper_All_Sheets"

Gord Dibben Excel MVP

There is no such example for this on Chip's page, but you
probably want to change the
MyCell = UCase(MyCell)
to
MyCell.formula = UCase(MyCell.formula)
so you don't wipe out formulas.

My own solution would be "Back to Kindergarten" in
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
which should be considerably, and ignores formulas and
empty cells. Your use of UsedRange will eliminate the
vast ocean of empty cells, but not those within the used range.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hayeso said:
In VBA Editor

Sub MakeUpper()
Dim MySht As Worksheet, MyCell As Range
For Each MySht In ThisWorkbook.Sheets
For Each MyCell In MySht.UsedRange.Cells
MyCell = UCase(MyCell)
Next
Next
End Sub
Click to expand...
Click to expand...
 
Top