Is it possible to change and entire sheet to Upper case???

Hi,

Is it possible to change and entire sheet to Upper case using the 'Upper' function?

We are need to change all the text to caps but can only seem to apply the function to one cell at a time and then copy it down for the column. This is going to be a fairly labour intensive process as we have many columns. Is there an easier way to achieve this.

Thanks in advance

Timbo

If you would rather not use formulas, you could right-click on the sheet tab, select view code, and paste this code, and then run the macro:

Sub allcaps()
    Dim rng As Range, cell As Range
    Set rng = ActiveSheet.UsedRange
    For Each cell In rng.Cells
        cell.Value = UCase(cell.Value)
    Next
End Sub

Thanks Bryan and Jason.

Jason, that macro is great. Thats going to save us heaps of work.


Thanks very much for your help. All the best for the year ahead.

Timbo

Glad I could help, Timbo.

That is awesome! I have been trying for EVER to do this with different formulas...and it two seconds the code you posted took care of my whole sheet. Why haven't MicroSoft people figured out a way to simplify this for the user yet?? Ugh. Anyway..thanks so much for posting.

This worked great! Thanks!

Thank you so much for this macro!! This made a WORLD of difference for a work project of mine.

You. Are. A. Genius! Thank you!!!

thanks a ton, this saved my time and did help me a lot and many thanks once again

Thanks Jason, your Macro saved me. I was looking for many days to convert the whole sheet in Caps but could not find any. Your macro has resolved my problem, thanks a lot.

Jason: A million thanks for the Macro! You just saved me soooo much time!

Do you have this macro for lower case. I would be very grateful. Thanks

Hi,

Is it possible with this macro to change the case to the Proper format? Just like the PROPER formula works on a cell? First letter is capitalized after any character other than a letter? Thanks!

Genius! Thank you so much!!

just a quick question (with probably a simple answer)
i want to change an entire worksheet to proper case (which is done with the code you posted, but i also have some columns that will have formulas in them. when you change a worksheet to proper, if you dont omit cells that have formulas in them they will change to text (or numbers), but i need to keep the formula intact. any help would be greatly appreciated.
thanks. jake

Originally Posted by stephenloky

Sub proper()
    Dim rng As Range, cell As Range
    Set rng = ActiveSheet.UsedRange
    For Each cell In rng.Cells
        cell.Value = WorksheetFunction.proper(cell.Value)
    Next
End Sub

That should do the work!

Hi I was trying to use this solution for a sheet I have, but it deleted all of my formulas.
Is there a way to make the macro not do that?

Sub allcaps()
Dim rng As Range, cell As Range
Set rng = ActiveSheet.UsedRange
For Each cell In rng.Cells
cell.Value = LCase(cell.Value)
Next
End Sub

Sub jec()
 Dim it
 For Each it In ActiveSheet.UsedRange.SpecialCells(2)
   it.Value = LCase(it)
 Next
End Sub

that's the ticket!
Thank you so much!