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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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>
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,218,909
Messages
6,145,169
Members
450,592
Latest member
Boulder127

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
Back
Top