Excel

Change Case of Selected Text

Ease of Use

Easy

Version tested with

2000 

Submitted by:

Anne Troy

Description:

Changes the case of selected text to all upper case or all lower case text. 

Discussion:

Suppose you get a dozen data files to incorporate into one, and many of them have the state in mixed formats, such as Pa, PA, pa, and Pa. You can simply do a find (period) and replace (nothing) to get rid of the periods, and run this code on the selected cells to change them all to upper case. You can use this code in any file if you store it in your personal.xls file. 

Code:

instructions for use 

			
Option Explicit 
 
Sub ConvertCase() 
    Dim Rng As Range 
    For Each Rng In Selection.Cells 
        If Rng.HasFormula = False Then 
             'Use this line for UpperCase text; change UCase to LCase for LowerCase text.
            Rng.Value = UCase(Rng.Value) 
        End If 
    Next Rng 
End Sub

How to use:

  1. Copy the code above.
  2. Open Excel, then open any workbook in which you want to use the code.
  3. Hit Alt+F11 to open the Visual Basic Editor (VBE) window.
  4. Double-click ThisWorkbook and paste the code into the code window at right.
  5. Change the code for lower case text if desired.
  6. Save the file and close the VBE.
 

Test the code:

  1. Select a range of cells.
  2. Hit Tools-Macro-Macros and double-click ConvertCase.
 

Sample File:

changecase.zip 6.01KB 

Approved by mdmackillop


This entry has been viewed 170 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express