uppercase (solved)

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
How do I convert an existing sheet's data to all uppercase?
This message was edited by NCay on 2002-09-17 21:14
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
what...for each individual cell!? I need to do the whole sheet.
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Hi,

Try the following code:

<pre>Sub test()
Dim Cell As Range

For Each Cell In ActiveSheet.UsedRange
If Not Cell.HasFormula Then Cell = UCase(Cell)
Next Cell

End Sub</pre>
 

anno

Board Regular
Joined
Feb 16, 2002
Messages
202

ADVERTISEMENT

or use the format painter (standard toolbar). type something in upper case into a cell and with this cell selected, double click the format painter. click on the top left cell you want 'uppered', hold down the shift key and click on the bottom right cell to be uppered and press enter.
hth
This message was edited by anno on 2002-09-17 20:34
 

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
Anno, thought you may have become my saviour there but...that pastes whatever I type across every cell selected, I need to keep the original data and just uppercase it all, so close but yet so far...
 

anno

Board Regular
Joined
Feb 16, 2002
Messages
202

ADVERTISEMENT

so it does. bugger. sorry about that. i was going to suggest an array using =UPPER() but you lose all your cell formatting. does jay's vba suggestion not work for you?


...that pastes whatever I type across every cell selected,
This message was edited by anno on 2002-09-17 20:51
 

anno

Board Regular
Joined
Feb 16, 2002
Messages
202
ok, slightly more convoluted way to do this but it should work. before you do this though note that if you have any formulas they will be gone.

select a range of cells the same size as those you want to change to upper case. in the formula bar type =UPPER(your cell range) and press ctrl+shift+enter simultaneously. now copy the array, select the first cell of your old range and go to edit>paste special>values. this should make everything upper case and preserve formats.
 

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
So close and yet so PERFECT, thanks a lot, it works a dream.
:biggrin:
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Here's an easier way to do it with a macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("A5").Value = UCase(Range("A5").Value)
End Sub

Instead of A5, simply put the array that you need.
 

Forum statistics

Threads
1,143,923
Messages
5,721,557
Members
422,370
Latest member
A Nonomus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top