How to change case in excel using VBA

Suppose we have to change the case of a word in between data in excel and there is lots of data in excel sheet then we can do this task from two technique first, we use excel function or second, we use VBA code. If we use the function for changing the case of the word then we have to use the function in separate place in excel sheet but if we use VBA code for changing the case of the word then we do not need to separate place in excel sheet for VBA, we can use it in between data.

how to change case in excel using vba

We have to write some codes for this task.

How to change lowercase letters to uppercase in excel using VBA

How to change Proper case letters to uppercase in excel using VBA

Steps

(1). First of all, we should disable Screen Updating and Display Alert. This may help to code run fast. This is not necessary but this is good practice for programming.

       With Application

           .ScreenUpdating = False

           .DisplayAlerts = False

       End With

(2). Declare Variable as per task

       Dim Single_Cells As Range

       Dim Data As Range

(3). Assign Range object as a reference to an Object Variable

       Set Data = Sheet1.Range(“A1”).CurrentRegion

(4). We use For Each Loop for this task

       For Each Single_Cells In Data

(5). We change case into uppercase word

       Single_Cells.Value = VBA.UCase(Single_Cells.Value)

(6). Next Object

       Next Single_Cells

(7). After that, we should enable Screen Updating, Display Alert.

        With Application

           .ScreenUpdating = False

           .DisplayAlerts = False

        End With

Here is full code to change case into upper word. 

Sub Upper_Case()

    With Application

        .ScreenUpdating = False

        .DisplayAlerts = False

    End With

    Dim Single_Cells As Range

    Dim Data As Range

    Set Data = Sheet1.Range(“A1”).CurrentRegion

    For Each Single_Cells In Data

        Single_Cells.Value = VBA.UCase(Single_Cells.Value)

    Next Single_Cells

    MsgBox “Done !!!”

    With Application

        .ScreenUpdating = True

        .DisplayAlerts = True

    End With   

End Sub

 

How to change Upper case letters to Lower Case in excel using VBA

How to change Proper case letters to Lowercase in excel using VBA

Steps

(1). First of all, we should disable Screen Updating and Display Alert. This may help to code run fast. This is not necessary but this is good practice for programming.

       With Application

           .ScreenUpdating = False

           .DisplayAlerts = False

       End With

(2). Declare Variable as per task

       Dim Single_Cells As Range

       Dim Data As Range

(3). Assign Range object as a reference to an Object Variable

       Set Data = Sheet1.Range(“E1”).CurrentRegion

(4). We use For Each Loop for this task

       For Each Single_Cells In Data

(5). We change case into lower case word

       Single_Cells.Value = VBA.LCase(Single_Cells.Value)

(6). Next Object

       Next Single_Cells

(7). After that, we should enable Screen Updating, Display Alert.

        With Application

           .ScreenUpdating = False

           .DisplayAlerts = False

        End With

Here is full code to change case into Lower word. 

Sub Lower_Case()

    With Application

        .ScreenUpdating = False

        .DisplayAlerts = False

    End With   

    Dim Single_Cells As Range

    Dim Data As Range

    Set Data = Sheet1.Range(“E1”).CurrentRegion

    For Each Single_Cells In Data

        Single_Cells.Value = VBA.LCase(Single_Cells.Value)

    Next Single_Cells

    MsgBox “Done !!!”

    With Application

        .ScreenUpdating = True

        .DisplayAlerts = True

    End With

End Sub

 

 

How to change Upper case letters to Proper Case in excel using VBA

How to change Proper case letters to the Proper case in excel using VBA

Steps

(1). First of all, we should disable Screen Updating and Display Alert. This may help to code run fast. This is not necessary but this is good practice for programming.

       With Application

           .ScreenUpdating = False

           .DisplayAlerts = False

       End With

(2). Declare Variable as per task

       Dim Single_Cells As Range

       Dim Data As Range

(3). Assign Range object as a reference to an Object Variable

       Set Data = Sheet1.Range(“I1”).CurrentRegion

(4). We use For Each Loop for this task

       For Each Single_Cells In Data

(5). We change case into proper case word

       Single_Cells.Value = VBA.StrConv(Single_Cells.Value, vbProperCase)

(6). Next Object

       Next Single_Cells

(7). After that, we should enable Screen Updating, Display Alert.

       With Application

           .ScreenUpdating = False

           .DisplayAlerts = False

       End With

Here is full code to change case into Proper word. 

Sub Proper_Case()

    With Application

        .ScreenUpdating = False

        .DisplayAlerts = False

    End With

    Dim Single_Cells As Range

    Dim Data As Range

    Set Data = Sheet1.Range(“I1”).CurrentRegion

    For Each Single_Cells In Data

        Single_Cells.Value = VBA.StrConv(Single_Cells.Value, vbProperCase)

    Next Single_Cells

    MsgBox “Done !!!”

    With Application

        .ScreenUpdating = True

        .DisplayAlerts = True

    End With   

End Sub

 

Download Workbook

Leave a Reply

Your email address will not be published. Required fields are marked *