Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the entire contents of my spreadsheet to uppercase
Does anyone know an easy way, or any way to change the entire spreadsheet to
upper case lettering? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the entire contents of my spreadsheet to uppercase
Brian
Macro is easiest for entire sheet. Just select all cells with CTRL + a(twice in 2003) and run the macro. 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 MS Excel MVP On Sun, 19 Nov 2006 12:13:01 -0800, Brian Denny <Brian wrote: Does anyone know an easy way, or any way to change the entire spreadsheet to upper case lettering? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the entire contents of my spreadsheet to uppercase
Sub Upshift()
Dim cell As Range For Each cell In Activsheet.UsedRange If Not cell.HasFormula Then cell.Value = UCase(cell.value) End If Next cell End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Brian Denny" <Brian wrote in message ... Does anyone know an easy way, or any way to change the entire spreadsheet to upper case lettering? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the entire contents of my spreadsheet to uppercase
And runs even better if you add an "e" to Activsheet<g
Gord On Sun, 19 Nov 2006 20:33:28 -0000, "Bob Phillips" wrote: Sub Upshift() Dim cell As Range For Each cell In Activsheet.UsedRange If Not cell.HasFormula Then cell.Value = UCase(cell.value) End If Next cell End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the entire contents of my spreadsheet to uppercase
Gord,
This looks great, but I have no idea how to run this macro. Under Tools I see macros, and recording just records keystrokes, and the other selection asks me to select a stored macro and there are none. Do I copy/paste this somewhere and name it and then run? Please advise. Thanks again. Brian Denny "Gord Dibben" wrote: Brian Macro is easiest for entire sheet. Just select all cells with CTRL + a(twice in 2003) and run the macro. 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 MS Excel MVP On Sun, 19 Nov 2006 12:13:01 -0800, Brian Denny <Brian wrote: Does anyone know an easy way, or any way to change the entire spreadsheet to upper case lettering? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the entire contents of my spreadsheet to uppercase
Ok, how about this. I copied the entire column to a Word page. At the
bottom of the column clicked on the past icon, then selected change style. From there selected Format, change case, then selected change to uppercase and ok. Copied the new column with all uppercase and pasted it back to the excel spreadsheet. Probably the hard way around, but got the job done. Would still like to know how to run a written macro if you have the time to explain sometime. Thanks, Brian Denny "Gord Dibben" wrote: Brian Macro is easiest for entire sheet. Just select all cells with CTRL + a(twice in 2003) and run the macro. 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 MS Excel MVP On Sun, 19 Nov 2006 12:13:01 -0800, Brian Denny <Brian wrote: Does anyone know an easy way, or any way to change the entire spreadsheet to upper case lettering? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the entire contents of my spreadsheet to uppercase
That worked very well. Thanks again!
Brian Denny "Gord Dibben" wrote: Apologies Brian.....had the idea in my mind that you were VBA conversant. I usually append the following to my VBA posts. If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Sun, 19 Nov 2006 12:45:02 -0800, Brian Denny <Brian wrote: Gord, This looks great, but I have no idea how to run this macro. Under Tools I see macros, and recording just records keystrokes, and the other selection asks me to select a stored macro and there are none. Do I copy/paste this somewhere and name it and then run? Please advise. Thanks again. Brian Denny "Gord Dibben" wrote: Brian Macro is easiest for entire sheet. Just select all cells with CTRL + a(twice in 2003) and run the macro. 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 MS Excel MVP On Sun, 19 Nov 2006 12:13:01 -0800, Brian Denny <Brian wrote: Does anyone know an easy way, or any way to change the entire spreadsheet to upper case lettering? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Resize individual cells w/o changing the entire column width | Excel Discussion (Misc queries) | |||
How do I change uppercase to proper case in entire worksheet? | Excel Worksheet Functions | |||
How do I change everything in my spreadsheet to all UPPERCASE? | Excel Discussion (Misc queries) | |||
paste contents from other spreadsheet | Excel Worksheet Functions | |||
Using PROPER for Columns, rows or ENTIRE spreadsheet | Excel Worksheet Functions |