How to: change entire Excel spreadsheet to all Caps

[Simon22]

Hi all,
I have a 10 page Word document with embedded excel sheets and the client has decided that they want it all in capitals now. How would I go about converting all letters to capitals it the whole document without having to retype it all?

 

[TulsaJeff]

luckily this is an easy one...

You need to create a module with the following code:

Sub caps()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Value = UCase(Cell.Value)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

I don't know where you are in relation to advanced Excel usage...if you need help with this I could send you the file and you could copy the module.

once you have created the module you simply select all your text...go to tools/macros and select the macro called caps...hit the run button and presto! we have caps.

I can give you a little more detailed help if you need it. let me know.

 

[Simon22]

Thank you very much. That worked beautifully