Proper, and other Text changes -- Use of SpecialCells

Location: http://www.mvps.org/dmcritchie/excel/proper.htm
Coding:http://www.mvps.org/dmcritchie/excel/code/proper.txt
Home page:  http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

If you want the contents of formulas to also be converted use Bill Manville’s code or a variation. (see related below)

This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to my  «Getting Started with Macros« or delve into it deeper on my Install  page.

Coding for this page, is available in another directory with names corresponding to some of the HTML files in the current directory.

A Document Map (Table of Contents of HTML headings H1-Hn) for this page or any other webpage can be generated as a sidebar with a Firefox addon (sorry IE solutions I've seen are not free, nor as elegant, such things may become available for IE7 users later on as it improves)

keywords:  capitalization, lettercase, lower case, lowercase, lower-case, proper case, title case, upper case, upper-case, uppercase

«Advantages of a macro:« while you can use  the worksheet functions  PROPER, UPPER, LOWER to change cells one at at time into another cell it is unlikely that you want to maintain two cells with the same value.   First you had to insert a helper column then carefully use the fill-handle to include all cells but not go too far (usually a double-click).  To clean up after worksheet functions you have to remove the dependency on the original by converting to constants (Copy using Ctrl+C then Edit, Paste Special, values) then you can delete the column of original entries.  With a macro the change is done in place, you can select entire columns or other selection, invoke the macro and you are finished.  A properly written macro will not process empty cells nor cells beyond the UsedRange and that is where the use of SpecialCells comes in.

Warnings:  If you have values that are defined as text values by preceding with a quote.  The macros regardless of whether they use .formula or .value will select text values based on SpecialCells, but will remove the single quote and text values that look like numbers would then become numbers if the format is General.

SpecialCells -- Usage warning, it has recently come to our attention (2004-01-11) that Special Cells has the following limitation (MS KB 832293).  If you select more than 8,192 non-contiguous cells with your macro, as may easily do with SpecialCells, Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros.  The inexcusable problem is that there is no error code using VBA.  Particularly a problem when deletion or other permanent changes are involved with a selection resulting from SpecialCells.  Manually you would at least get:  The selection is too large.  (May be ranges not cells).  Ron de Bruin has written a page on SpecialCells limit problem.  Also see Coloring of cells based on CellType which also serves as a Demonstration of Failure in SpecialCells -- if you were deleting rows you could be in big trouble.

 ABC
 1 -2   #DIV/0! -2 
 2 #DIV/0!  2  #DIV/0!
 3 -4   #DIV/0! -4 

You can use this formula to create a lot of discontiguous error cells with: test with Range("A1:F3096")
   =EVEN(ROW())/(ROW()-EVEN(ROW())-COLUMN()+ODD(COLUMN()))
Use this code in intermediate area to select formula cells without errors (or use a subroutine)
   Selection.SpecialCells(xlCellTypeFormulas, 7).select
When you should have more than 8192 areas, all cells will remain selected in original selection area.

I use SpecialCells frequently on this page and on my reformatting (join.htm, Delete Empty Cells/Rows based on Empty
See Coloring of cells based on CellType, also serves as a Demonstration of Failure in SpecialCells.

Sections:  #proper. #lower, #upper, #trimsub, #insertblankrows, #intersect, #failure to use Special Cells.

PROPER   (#proper)

You can change your all uppercase to PROPER case with a worksheet function.  This can help with a list of names and addresses that was in all uppercase (capitals) or lowercase.  A mere change to Proper Case will not fix names like “McRitchie” or names like “van der Beck”; therefore, as an example, changes have been made to fix names that begin at the leftmost character of a cell such as those beginning with “Mc”, “Mac”, “O'”, “van der ”, “van ”, “von ”, and “de ”, but not “Mack” because there are too many exceptions to how some people capitalize their name even if spelled the same as someone else.  Again these are my exceptions you can code your own exceptions.  If there is nothing to the left of the lastname in a cell this is how the folowing names will appear: MacAuley, MacDonald, Mack, Mackey, Mackney, MacManus, MacPherson, Makin, McAndrew, McDonald, McRitchie, Oates, O'Brian, O'Connell, Oddy, O'Hara, Olsen, van Benton, van der Zwan, van Milligen. 

If you have numbers indicating a generation, you can process them easily if they follow a comma and make excpetions ", II", ", III", ", IV".

Exceptions for Title Case, are included for embedded words of -- a, and, at, for, from, in, of, on,  /and/  the

HELP --> Index --> PROPER Worksheet Function

=PROPER(a1)

Using a worksheet function you would have to copy then paste special and delete the original afterwards.

A macro works a lot easier.  Simply select a range of cells and invoke the Proper_case macro.  Additional code is included in the macro to speed up results. 

Install the three macros below:  the proper_case macro calls the proper_case_inner macro, and CapWords macro is called by the proper_case macro.  The code for all three plus some additional related macros can be picked up on the corresponding code/proper.txt page.  Manually you invoke the Proper_case macro, i.e. from Alt+F8.  The proper_case_inner macro can be called from another macro to use a specified range instead of the current selection.  The following macro code can be copied directly from code/proper.txt.

   Sub Proper_case()
      '-- This macro is invoked by you -- i.e. from Macro Dialog (Alt+F8)  
      proper_case_inner    'The macro you invoke from a menu is Proper_Case
   end sub
   Sub Proper_Case_Inner(Optional mySelection As String)
     '-- The parameter here can only be used if invoked from another macro 
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     Dim Cell as Range
     Dim Rng As Range
     On Error Resume Next   'In case no cells in selection
     If mySelection =  " " Then Set Rng = Selection _
         Else Set Rng = Range(mySelection)
     For Each Cell In Intersect(Rng, _
            Rng.SpecialCells(xlConstants, xlTextValues))
        Cell.Formula = StrConv(Cell.Formula,vbProperCase)
        '--- this is where you would code generalized changes for lastname
        '--- applied to names beginning in position 1 of cell
        If Left(Cell.Value, 2) = "Mc" Then Cell.Value = _
           "Mc" & UCase(Mid(Cell.Value, 3, 1)) & Mid(Cell.Value, 4, 99)
        If Left(cell.Value, 3) =  "Mac " _
               And Left(cell.Value, 4) <>  "Mack " Then cell.Value = _
            "Mac " & UCase(Mid(cell.Value, 4, 1)) & Mid(cell.Value, 5, 99)
           '-- do not change Mack   Mackey  Mackney  or any Mack...
        If Left(cell.Value, 2) = "O'" Then cell.Value = _
           "O'" & UCase(Mid(cell.Value, 3, 1)) & Mid(cell.Value, 4, 99)
        If Left(cell.Value, 8) = "Van Den " Then cell.Value = _
            "van den  " & Mid(cell.Value, 9, 99)
        If Left(cell.Value, 8) = "Van Der " Then cell.Value = _
           "van der " & Mid(cell.Value, 9, 99)
        '-- single parts after those with two part prefixes
        If Left(cell.Value, 3) =  "Vd " Then cell.Value = _
            "vd " & Mid(cell.Value, 4, 99)
        If Left(cell.Value, 4) =  "V/D " Then cell.Value = _
            "v/d " & Mid(cell.Value, 5, 99)
        If Left(cell.Value, 4) =  "V.D " Then cell.Value = _
            "v.d " & Mid(cell.Value, 5, 99)
        If Left(cell.Value, 3) = "De " Then cell.Value = _
           "de " & Mid(cell.Value, 4, 99)
        If Left(cell.Value, 4) = "Van " Then cell.Value = _
           "van " & Mid(cell.Value, 5, 99)
        If Left(cell.Value, 4) = "Von " Then cell.Value = _
           "von " & Mid(cell.Value, 5, 99)
     Next   
     '-- some specific text changes to lowercase, not in first position
     rng.Replace what:= " a ", replacement:= " a ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " and ", replacement:= " and ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " at ", replacement:= " at ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " for ", replacement:= " for ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " from ", replacement:= " from ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " in ", replacement:= " in ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " of ", replacement:= " of ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " on ", replacement:= " on ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " the ", replacement:= " the ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     '--- This is where you would code specific name changes
     '--- regardless of position of character string in the cell
     rng.Replace what:= "mcritchie ", replacement:= "McRitchie ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
     CapWords (mySelection)  'activate if you want to run macro
  End Sub

  Sub CapWords(Optional mySelection As String)
    'Expect all substitutions here would be to capitals
    'not necessarily limited to words
    Dim savCalc As Long, savScrnUD As Boolean
    savCalc = Application.Calculation
    savScrnUD = Application.ScreenUpdating
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Dim rng As Range
    On Error GoTo done   'In case no cells in selection
    If mySelection =  " " Then Set rng = Selection _
         Else: Set rng = Range(mySelection)
    rng.Replace What:= "IBM ", Replacement:= "IBM ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
  done:
    Application.Calculation = savCalc
    Application.ScreenUpdating = savScrnUD
  End Sub
The Proper_Case macro had customizations that I wanted to use in other macros so I renamed it to Proper_Case_Inner and created another macro named Proper_Case that would still show up on the Macro List (Ctrl+F8).  The Proper_case_inner macro accepts optional parameters like the range, which can only be specified in a macro called by another macro.  The CapWords macro will not show up in list of macros (Alt+F8) but can be used directly or from another macro.

You can put it into a menu like I do, or you can assign a shortcut key to it of Ctrl+Shift+P so it doesn’t interfer with the Excel shortcut of Ctrl+p for Print.  You can check for conflicts with preassigned shortcut keys by using your browser’s FIND (Ctrl+F) on the Shortcuts web page, where you will also find instructions for “Creating your own shortcut keys”.

Use  StrConv  instead of application.proper (#strconv)

Use   Cell.Formula = StrConv(Cell.Formula,vbProperCase)
instead of Cell.Formula = application.proper(cell.formula)

Not only because the VBA is faster but because it won't change words like "doesn't" to "Doesn'T".

Some notations on the above code, and use of Special Cells (speed and efficiency considerations) « (#notations)

Example of Current Region involved when a single cell is selected

As previously described here is an example where a single cell is selected but the use of “Selection.SpecialCells(xlCellTypeConstants)” would cause cell involvement from the current region instead of a maximum of 1 cell.
  Sub DemoSingleCellSelect()
    Dim cell As Range
    Range("a1:k10") = "aa"   '-- try later with  = "=1+1"
    Range("a3:f4").Clear
    Range("a1").Select
    on error resume next    '-- in case no cells selected
    For Each cell In Selection.SpecialCells(xlCellTypeConstants)
       cell.Interior.colorindex = 38
   Next cell
  End Sub

Things to watch out for when using macros, your own or someone else’s

Evaluating various solutions including your own -- «Recap«   (#recap)

How much of a difference can use of SpecialCells really make (#speed)

My laptop speed is 600mHz.  Here are some timings based on a macro you can find in my Slow Response page.
(New laptop in Aug 2007 is DELL Inspiron 1521 with 1.60 gigahertz AMD Turion 64 X2, so bad coding may be slightly less noticeable.)

The following took 366.066 seconds without reducing the selection to cells with actual data, in other words it processed every single cell in coumn E, there are only 22 rows even used.  (Note that was when Excel had only 64,436 rows, Excel 2007 has over a million rows with new worksheets not in compatibility mode, so a faster machine with more memory won't cover up bad code)

  Dim i As Long
  For i = 1 To Cells.Rows.Count
    Cells(i, 5).Formula = _
    Application.WorksheetFunction.Proper(Cells(i, 5).Formula)
  Next i
Same thing using Special_cells took 0.040 seconds
   Dim cell As Range
   For Each cell In Columns("E").SpecialCells(xlConstants, xlTextValues)
        'cell.Formula = Application.Proper(cell.Formula) 'see strconv notes
        Cell.Formula = StrConv(Cell.Formula,vbProperCase)
   Next cell
invoking the proper_case macro took 0.070 seconds whether changes were actually made or not, involving excuting an external macro, and extra code for exceptions with some words after first word to appear in lowercase (and, the, on, a) to appear in lowercase,and other exceptions.  The examples above just record the times for part of the coding shown.
   Columns("E").Select
   call  'personal.xls'!Proper_Case
Using Tom’s example calling macro installed in same workbook took 0.491 seconds, or if modified for only .Columns("e"). instead of .UsedRange. took 0.050 seconds, or 0.040 seconds if no updating changes needed.  Note Screen Updating and Calculation were not turned off.  With Screen Updating and Calculation turned off it took 0.030 seconds with or without changes actually being made.  Had several screens of data been involved the differences would have been much greater.
   MakeProper

Example from Tom Ogilvy

Here is a solution from Tom Ogilvy 1999-02-07) that changes cells that are constants on the ENTIRE spreadsheet.  The macro provided at the top will do the same if you Select ALL cells first before invoking the macro, but you might want to compare what is the same and what differs.  One big difference will be the speed because it has not turned off calculation and screen updating, which would be very noticeable if there are a several screens of data involved.

   Sub MakeProper()
     Dim Cell as Range, rng1 as Range
     Set rng1 = ActiveSheet.UsedRange.SpecialCells(xlConstants, xlTextValues)
     For Each cell In rng1
        'cell.Formula = Application.Proper(cell.Formula) 'see strconv notes
        Cell.Formula = StrConv(Cell.Formula,vbProperCase)
     Next cell
   End Sub
For XL97 and above use xlCellTypeConstants instead of xlConstants

Additional considerations for speed and efficiency can be seen in Slow Response and Memory Problems, Speeding up Excel, Enhancing Performance

An anomaly from Dave Peterson (#anomaly)

The following works without looping individual cells, (2003-03-21), but requires processing of each area even in Excel 2000 and 2002 to prevent some #N/A! errors and grabbing data from the wrong areas either of which would destroy the data making the solution without using areas useless.  Range test included in case the selection occurs entirely outside the used range.
  Sub MakeProper_Quick_test()
    Range("A1").Formula = "=""asdf ""&ADDRESS(ROW(),COLUMN(),4)&"" qwer"""
    Dim i As Long
    i = InputBox("type 1 to convert all to values", "values", 1)
    If i = 1 Then
        Cells.Copy
        Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End If
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:H1"), Type:=xlFillDefault
    Range("A1:H1").Select
    Selection.AutoFill Destination:=Range("A1:H29"), Type:=xlFillDefault
    Range("A1:H29").Select
    Range("B5:F17,H6:H12,D21:D25,G20:G26,B23:B27").Select
    Range("B23").Activate
    Application.Run "MakeProper_Quick"
  End Sub
 
  Sub MakeProper_Quick()
   'Dave Peterson, 2003-03-21, misc,  no loop required...
   '-- doesn't work with application.upper and application.lower
    Application.ScreenUpdating = False
    Dim myRng As Range
    Dim myArea As Range
    On Error Resume Next
    Set myRng = Intersect(Selection, ActiveSheet.UsedRange)
    If myRng Is Nothing Then
        MsgBox "Nothing in intersect range"
    Else
        For Each myArea In myRng.Areas
            myArea.Formula = Application.Proper(myArea.Formula)
            'myArea.Formula = Application.Proper(myArea.Formula) 'see strconv notes
        myArea.Formula = StrConv(myArea.Formula,vbProperCase)
        Next myArea
    End If
    Application.ScreenUpdating = True
  End Sub
Please see the thread.  The usage results are not consistent unless one uses Areas but is still faster than going cell by cell.

Invoking a Change Event macro to change to Proper Case (#proper_change)

Change Event macros installed differently than regular macros.  More information on Event Macros

Either of the Change Event macros below can be installed with right-click on sheet tab, view code, plop code into module.

The following Change Event macro will invoke the Proper_case macro when a cell in Column D (col 4) is changed.  The Proper_Case macro is the first macro shown in this article.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Row  = 1 then Exit Sub 'don't override headings in row 1 
  If Target.Column <> 4 Then Exit Sub  'only allow changes to Col D 
  Application.EnableEvents = False
  Application.Run "personal.xls!Proper_Case", Target.Address
  Application.EnableEvents = True
End Sub
If you don’t want the additional features of the Proper_Case macro you can use the following code, but it will probably cause you more grief than help due to inability to override, not even by changing another macro.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Column <> 4 Then Exit Sub
  'target.Formula = Application.Proper(target.Formula) 'see strconv notes
        target.Formula = StrConv(target.Formula,vbProperCase)
End Sub
Disadvantages of a Change Event Macro
–   You cannot override the change even manually not even with copy and paste, except by deactivating the event macro.
–   If you move the columns around, you will have to change the macro accordingly.  To minimize damage to a column, you might check the row 1 value in the column before changing data in the column.
 
End of Material on Proper Capitalizations, beginning with the PROPER Worksheet Function.
This section also included introductory notes on the speeding up macros with the use of Intersect and SpecialCells.

Lowercase   (#lower)

The worksheet function LOWER works the same as the PROPER worksheet but converts to lower case. function.

=LOWER(B2)

Example of a macro: (turns off screen updating and calculation in macro for Quicker results)

Sub Lower_Case()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual          'in XL97
   Dim Cell as Range
   On Error Resume Next   'In case no cells in selection
   For Each Cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
      Cell.Formula = LCase(Cell.Formula)
   Next
   Application.Calculation = xlCalculationAutomatic     'in XL97
   Application.ScreenUpdating = True
End Sub

Uppercase   (#upper)

The worksheet function UPPER works the same as the PROPER worksheet function but converts to upper case. 

=UPPER(B2)

I would have to say that except for zip state codes I have not much use for converting everything to uppercase.

Example of a macro: (turns off screen updating and calculation in macro for Quicker results)

Sub Upper_Case()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim Cell as Range
   On Error Resume Next   'In case no cells in selection
   For Each Cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
      Cell.Formula = UCase(Cell.Formula)
   Next
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
If appearance is all that matters there are some uppercase only fonts like Bank Gothic Mb BT, Copper Plate Gothic [Hv] BT (Cooperpot), Desdemona, Bassoon.  You can find what you actually have by bringing the Unicode Character Map (shortcut) typing in your name in mixed case, and using the ArrowDN key to cycle through the fonts.  Excel and other windows software does not provide access to codepages which could have been built to have upppercase only.  Subset under font is as close as you will get to codepages.  For an Excel solution see Copper Plate below.

Apply UpperCase selectively to some columns on a sheet (#ucase_event)

The following would change entries immediately as they are created using an Event macro.  An Event Macro is apparently not effective until the workbook containing the macro is saved.  Note event macros are stored with the worksheet unlike normal macros which are stored in modules.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'.  Paste the following procedure
' in the module.  -- this is for EVENT macros ONLY.
  If Target.Column = 1 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
  If Target.Column = 6 then
    on error resume next  'insure that Enable Events gets reenabled
    Application.EnableEvents = False
    Target.Formula = UCase(Target.Formula)
    Application.EnableEvents = True
    on error goto 0
  end if
End Sub
It would be perhaps more common to only do this for say a zipstate code in something like column 6, where you might include
    If Target.Column <> 6 Then Exit Sub

Back to Kindergarten   (#kindy)

There is always the occassional request to change the text in all sheets to uppercase.  As much as I dislike very many things in uppercase here is a solution.
Sub KindergartenFont()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim x As String, ws As Object, cell As Range
   x = MsgBox("Use CANCEL to abort changing all constant " _
       & "cells to uppercase", vbOKCancel)
   If x = vbCancel Then Exit Sub
   For Each ws In ActiveWorkbook.Sheets
    On Error Resume Next   'In case no cells in selection
    ws.Activate
    For Each cell In Cells.SpecialCells(xlConstants, xlTextValues)
        cell.Formula = UCase(cell.Formula)
    Next
  Next ws
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Special capitalization and formatting for surnames   (#surname)

 ABCDEFG
 1 Phone  Member Address  City  ST  Zip+4 email
 2 412-555-1212   BROWN, Mark 4210 Washington Rd  Pittsburgh  PA 15228-3000   mbrown@example.com
 3 203-484-1234  TAYLOR 714 Old Post Rd North Branford   CT 06471   ktaylor@example.com
 4 505-924-1212  WATSON, James  240 E Main St Albuquerque NM   87103-8123  jim_watson@example.com 

This is a specialized example to capitalize and bold the surname, you can find the complete macros for doing this at Surnames, Special formatting for LASTNAME, firstname, using mostly techniques described on this page (proper.htm).  Coding makes use of code like this, which is actually limited to text constants.

      cell.Formula = UCase(cell.Formula)
      With cell.Font
        .FontStyle = "Bold"
      End With
      With cell.Characters(Start:=i + 1).Font
        .FontStyle = "Regular"
      End With
and because this has to be done for each cell there is a macro to change a selection of cells, and there is an Event macro to change anything entered as text constants into Column 2 (Column B).

Partial string lookups   (#partialstring)

Looking up part of the cell. For example you have a name and address list and want to get to the K's quickly, or even more specifically to a lastname beginning with Ken.  Another example:&nsp; if the cell contains “Dallas, Texas” it is to pick up the city of Dallas without regard to the comma and the remainder after the comma. See use of the FindFirstChar macro (code) described in posting (misc, 2002-10-31).  Also see use of Partial String on VLOOKUP page.

Capitalize Cells with Formulas and/or Constants   (#ucase_all)

The following will process all nonblank cells in the selection both formulas and constants, but will not waste time processing empty cells within the selection.  Do keep in mind warnings about use of noncontiguous SpecialCells usage.

This is an example of use the of  UNION  to combine two different SpecialCells ranges, and then to process them in one loop.

Sub Upper_Case_ALL()
    'David McRitchie, programming, 2003-03-07
    Dim rng1 As Range, rng2 As Range, bigrange As Range
    Dim cell As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
    Set rng1 = Intersect(Selection, _
       Selection.SpecialCells(xlCellTypeConstants))
    Set rng2 = Intersect(Selection, _
       Selection.SpecialCells(xlCellTypeFormulas))
    On Error GoTo 0
    If rng1 Is Nothing Then
       Set bigrange = rng2
    ElseIf rng2 Is Nothing Then
       Set bigrange = rng1
    Else
       Set bigrange = Union(rng1, rng2)
    End If
    If bigrange Is Nothing Then
       MsgBox "All cells in range are EMPTY"
       GoTo done
    End If
    For Each cell In bigrange
       cell.Formula = UCase(cell.Formula)
    Next cell
done:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub 
End of Material on Capitalized Cell Entries and Full Word capitalizations, beginning with UPPER Worksheet Function.

TrimSUB - trim cells in selection   (#trimsub)

The TRIM Worksheet function removes spaces from beginning and end of text cells as well as extra internal spaces.  Removes all spaces from text except for single spaces between words.  The VBA equivalent only trims leading and trailing spaces.  (turn off screen updating and calculation in macro for Quicker results).  See the TrimALL macro for a more robust version with additional explanations and code you would actually want to use.
Sub TrimSUB()
   Dim icell as Range
   For Each icell In Intersect(Selection, _
         Selection.SpecialCells(xlConstants, xlTextValues))
      icell.Formula = Trim(icell.Formula)
   Next icell
End Sub

Insert Blank Rows (#insertblankrows)

For this example 2001-10-02, the poster requested that 2 blank rows be inserted for when the next cell in selected column is greater than the cell in the previous row of the column.  Note use of INTERSECT.
Sub InsertBlankRows()
    Dim i As Long   'integer not sufficient
    Dim nRange As Range
    Set nRange = Intersect(Selection, ActiveSheet.UsedRange, _
       ActiveCell.EntireColumn)
    If nRange.Cells.Count < 2 Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For i = nRange.Cells.Count To 2 Step -1
       If nRange.Item(i) > nRange.Item(i - 1) And _
            Not IsEmpty(nRange.Item(i - 1)) Then
            nRange.Item(i).Resize(2, 1).EntireRow.Insert
       End If
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
The example was included here because of the use of INTERSECT and the manner of the loop, more comprehensive information on Inserting Rows.

Select cells matching activecell in the Selection area

Make new selections based on an original single (rectangular) selection based on the position of the active cell. In Excel 2000 you can use multiple selections with the Ctrl key which means the last selection, which can be a single cell, would be your active cell without any extra effort. (posted programming 2001-04-04).

With an initial selections of K8:Q15,K9:K9, would have K9 as the activecell

The resulting range of the example at right would be
Range("K9:K9,K10:K10,K11:K11,L11:L11,M11:M11,O11:O11," _
  &  "P11:P11,Q11:Q11,Q12:Q12,Q13:Q13,Q14:Q14,Q15:Q15").Select


Sub S_active()
  Dim cell As Range
  Dim i As Long
  Dim sStr As String
  For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
    i = i + 1
    If cell.Formula = ActiveCell.Formula Then _
       sStr = sStr & "," & cell.Address(0, 0) _
       & ":" & cell.Address(0, 0)
  Next cell
  Range(Mid(sStr, 2, Len(sStr) - 1)).Select
End Sub
K8L8M8N8O8P8 Q8
K9L9 M9N9O9P9Q9
K9L10 M10N10O10P10Q10
K9 K9 K9N11 K9 K9 K9
K12L12M12N12O12P12 K9
K13L13M13N13O13P13 K9
K14L14M14N14O14P14 K9
K15L15M15N15O15P15 K9

Additional Examples of Interesect and SpecialCellss   (#intersect)

The following are somewhat offtopic for Proper Case etc., but since this page is also concerned with SpecialCells have included a few additional examples. (meant SpecialCells not SpecialValues)
Sub Formulas_to_Values()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   On Error Resume Next   'In case no cells in selection
   For Each cell In Selection.SpecialCells(xlFormulas)
      cell.Value= cell.Formula
      If Trim(cell.Formula) = "" Then cell.Formula = ""
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

Checking for Numeric Cells in a Worksheet Selection   (#numeric)

The following may look a bit out of place on this page, but is included here because of use of celltypes ie. xlCellTypeFormulas, xlCellTypeConstants

You can make similar macros named chkText and chkAllText to check for text instead by using xlTextValues instead of the 1 used in place of xlNumbers.


Sub chkAllNumeric()
    Cells.Select
    chkNumeric
End Sub
Sub chkNumeric()
    'David McRitchie in misc  2001-04-14  will be in proper.htm
    '   http://www.mvps.org/dmcritchie/excel/proper.htm
    'Something similar can be done with C.F.  =ISNUMBER(A1)
    Dim fNumeric As Range, cNumeric As Range
    On Error Resume Next
    Set fNumeric = Selection.SpecialCells(xlCellTypeFormulas, 1)
    Set cNumeric = Selection.SpecialCells(xlCellTypeConstants, 1)
    If fNumeric Is Nothing Then
       If cNumeric Is Nothing Then
          MsgBox "No numeric cells in selection"
       Else
          cNumeric.Select
       End If
    Else
       If cNumeric Is Nothing Then
          fNumeric.Select
       Else
          Union(fNumeric, cNumeric).Select
       End If
    End If
End Sub
Note that use of UNION requires each range to not be empty (not nothing), to obtain a UNION of several ranges omitting the empty ranges you might do better with a loop, see Tom Ogilvy’s posting 2002-12-30.

An unreliable visual indicator would be to look at the cell: if left justified text, if right justified numberic.  This can be overridden by cell justification, so select the cell and make sure it is not specifically overridden with left/center/right justified.

To check a lot of cells would be to select all cells (Ctrl+A) then Edit menu, GoTo, [Special],
    Constants & Number
    Formulas & Number
Unfortunately you can’t do both at same time. Also note this is where you would choose "Blanks" to see if a cell is really Empty (no content, no formulas).

Use a Worksheet Function on an individual cell
    =ISTEXT(A1)
    =ISNUMBER(A1)

Use a Conditional Formatting with =ISNUMBER(A1) with one color and =ISTEXT(A1) with another color as described by Rob Bovey.  Conditional Formatting will do no damage to your Excel File.  To remove C.F. you will have to find C.F. usage which you can do by selecting a cell identified in the range and then Format --> C.F.  as was used in creation.

Clear Number Constants   (#clearnumberconstants)

Run the following macro to clear number constants from the selection area.  The macro will leave text constants, formulas, and formatting alone.
Sub ClearNumberConstants()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   On Error Resume Next   'In case no such cells in selection
   For Each cell In Intersect(Selection, _
       Selection.SpecialCells(xlConstants, xlNumbers))
      cell.Formula = ""
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

Convert all cells to text values   (#text)

Removes all formulas, converts all numbers to the text value, the text that you see displayed.  Everything will be text when finished so will be left justified.  If left justification is a problem see code for my Excel to HTML page.  Useful for some tricky conversions to HTML or with use in Mail Merge.  For your convenience this creates a copy of the active sheet so you don’t harm your original worksheet. 
Sub AllCellsToText()
   'D.McRitchie, posted 2003-01-17 worksheet.functions
   'specifically for use with Mail Merge
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   Dim str  As String
   '-- to change the activesheet comment out the .copy line below
   Sheets(ActiveSheet.Name).Copy Before:=Sheets(ActiveSheet.Name)
   On Error Resume Next   'In case no such cells in selection
   For Each cell In Cells.SpecialCells(xlFormulas)
      cell.Value = cell.Value
   Next cell
   For Each cell In Cells.SpecialCells(xlConstants, xlNumbers)
     str = cell.Text
     cell.NumberFormat = "@"
     cell.Value = str
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
For use with Mail Merge you want your text formatted sheet as the first worksheet in the workbook if using Excel 2000, so you should have the worksheet you want a text formatted copy of as the first sheet, or change the so it has =Sheets(1) instead of =sheets(ActiveSheet.Name the having the exc

AutoCorrect   (#autocorrect)

There are some things that cause people problems with Auto Correct (Tools menu, Autocorrect)

Text Comparison, Case Sensitivity (#compare)

In Excel most Worksheet Functions are case insensitive for comparisons so upper and lowercase letters compare equal.  Exceptions include FIND, and SUBSTITUTE which are case sensitive.  See strings page. 

Worksheet Functions specific to changing case are:  LOWER, PROPER and UPPER.

Worksheet Functions specific to exact comparison are:  EXACT
An Example that would be useful in Conditional Formatting or to colorize entries in all uppercase or all lowercase or to prevent entries not in mixed case with in Data Validation   =IF(A1<>"",OR(EXACT(A1,LOWER(A1)),EXACT(A1,UPPER(A1))))
As an alternative you could use an Event macro to change such entries immediately to proper case when entered:   

In VBA most comparisons are case sensitive and letter case makes a difference for comparisons. in a macro.  If you want a comparison of text without regard to letter case in a macro, you can use
   If ucase(var1) = ucase(var2) then ...

or faster using a text compare (1 or vbTextCompare)
   If StrComp(var1, var2, 1) = 0 then ...'  Returns 0 if values are textually equal.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True   'Get out of edit mode 
   Range("A1").Value = "Banana"
   MsgBox InStr(1, Range("A1").Value, "banana") > 0  'case sensitive
   MsgBox InStr(1, Range("A1").Value, "banana", vbTextCompare) > 0  'insensitive
   MsgBox Range("A1").Value Like "*banana*"     'case sensitive
   MsgBox LCase(Range("A1").Value) Like "*banana*"  'case insensitive
End Sub
VBA Functions specific to changing case are: LCASE and UCASE, for Proper you would have to use Application.Proper in your code to use the Excel function.  But better would be Cell.Formula = StrConv(Cell.Formula,vbProperCase) -- See strconv notes

More on Like and more information on strings.


Convoluted Methods   (#convoluted)

Suggestions are sometimes made to use MS Word to convert the lettercase in Excel.  See thread.  With a macro that uses SpecialCells you can select entire columns and only process cells with text content.  Processing empty cells in a macro or otherwise would take a very long time.

Some notes or postings on Failure to use Special Cells   (#failure)

    Also see warnings note on failure of SpecialCells when too many areas.

Turning off Events   (#problems)

Just in case you somehow manage to abend (terminate) a macro that turned of EnableEvents you will not be able to run any Event macros until you reenable events.  You will have to run a macro from a standard code module.
Sub Fix_Things()
   Application.DisplayAlerts = True 
   Application.EnableEvents = True    
End Sub
A more complete macro can be seen in the reset_things macro in
   http://www.mvps.org/dmcritchie/excel/code/proper.txt

How to install/use a macro can be found on my formula page and in Getting Started with macros as noted at the top of this page and most of my pages with macros.

HELP --> StrConv     [vbUpperCase, vbLowerCase, vbProperCase]
= StrConv(Cell,vbLowerCase) page.

Related Information on My Excel Pages:

Related Information in Google Usenet Archives

Microsoft Knowledge Data Base (MS KB)

Q107564 XL: Not All Worksheet Functions Supported as Application (in VBA)
Run-Time Error '438':
Object doesn’t support this property or method

Other places (#other)

Disable “Caps Lock” [LG].  I physically remove Caps Lock key except on a laptop where I use a barrier.  for better solutions see Caps Lock on my shortcut keys page.


You are one of many distinguished visitors who have visited my site here or in a previous location  since July 17, 1998. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2010,  F. David McRitchie,  All Rights Reserved