I want to change all rows or columns or cells to uppercase

[Mukund Vyas]

hello
i have list of names which in lowercase
i want it in uppercase but not in other cells, on same cells or column or rows
thats means replace to uppercase

 

[Deepak]

Hope This will do...

Use

Option Explicit

Sub Change_Case()
    Dim rng As Range, c As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rng = Cells.SpecialCells(xlCellTypeConstants, 2)
    For Each c In rng
        c.Value = UCase(c.Value)
    Next c
    Application.ScreenUpdating = True
End Sub

 

[Mukund Vyas]

Hope This will do...

Use

Option Explicit

Sub Change_Case()
    Dim rng As Range, c As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rng = Cells.SpecialCells(xlCellTypeConstants, 2)
    For Each c In rng
        c.Value = UCase(c.Value)
    Next c
    Application.ScreenUpdating = True
End Sub

thanks deepak for reply

how to use this formula

suppose there are name in a1, a2, a3
mukund
vyas
i want this column in uppercase then how to do this..

 

[Somendra Misra]

Hi Mukund,

Assuming name list in column A, use below code:

Sub Macro1()
 
With Sheet1
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
 
    For i = 1 To lastrow
        Cells(i, 1) = UCase(Cells(i, 1).Value)
    Next i
   
End Sub

Regards,

 

[Mukund Vyas]

Hi Mukund,

Assuming name list in column A, use below code:

Sub Macro1()

With Sheet1
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

    For i = 1 To lastrow
        Cells(i, 1) = UCase(Cells(i, 1).Value)
    Next i

End Sub

Regards,

thanks Somendra..

i copied this code by pressing alt+f11 then wher to paste it in module or sheet
i tried both but no effect
and i saved this file as macro enabled

 

[Somendra Misra]

@Mukund Vyas

Copy the code in a module by going into VBA editor by pressing Alt+F11, then run the code by pressing F5.

Regards,

 

[Deepak]

Earlier provided code will change case of each cell on active sheet.

Case : You wish to change the case of selection

Option Explicit

Sub Change_Case_Selection()
    Dim c As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each c In Selection
        c.Value = UCase(c.Value)
    Next c
    Application.ScreenUpdating = True
End Sub

Case : You wish to change the case of column A

Option Explicit

Sub Change_Case_A()
    Dim c As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For c = 1 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
       Cells(c, 1) = UCase(Cells(c, 1).Value)
    Next c
    Application.ScreenUpdating = True
End Sub

 

[Deepak]

Use : Alt+F11 > insert a module>copy-paste the above code as you required.
Alt+Q to close the VBA editor.
Than Alt+F8 to run the macro.

or Goto http://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

 

[Mukund Vyas]

Thank you Deepak & Somendra..

done
worked..

nice code Deepak for selection only..

thanks..

 

[Deepak]

Welcome !!!

 

[Mukund Vyas]

Hello...

Can we change case to Sentence case.. that mean mukund vyas to Mukund Vyas

 

[Deepak]

Change UCase(Cells(c, 1).Value) to StrConv(Cells(c, 1).Value, vbProperCase)
or
UCase(c.Value)  to StrConv(c.Value, vbProperCase)

 

[Mukund Vyas]

thanks... Deepak..

i have done like this...

Option Explicit

Sub Upper_Case_Selection()
    Dim c As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each c In Selection
        c.Value = UCase(c.Value)
    Next c
    Application.ScreenUpdating = True
End Sub

Sub Sentence_Case_Selection()
    Dim c As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each c In Selection
        c.Value = StrConv(c.Value, vbProperCase)
    Next c
    Application.ScreenUpdating = True
End Sub

 

[Deepak]

Hope! All is well.

 

[Mukund Vyas]

CAN YOU PLZ PROVIDE CODE FOR lower case also..

 

[Debraj]

Here is another approach..

* Copy cells (A1:A100).
* In win Word, paste as Special > Unformatted Text. (To Remove Table Feature)
* Press Ctrl + A (To Select All Words)
* Press Shift + F3 .. each press will change the case.. from noRmal > UPPER > lower >Sentence Case..

Now copy from Word & paste back to Excel..

PS: take care in case of multi line in single cell. but according to situation, you can handle the situations in word..

 

[Deepak]

Just Change UCase to LCase.