SpecialCells Method

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Select (Work With) Specific Cell Types in Excel

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help .

The SpecialCells Method in Excel VBA

One of the most beneficial Methods in Excel (in my experience) is the SpecialCells Method. When used, it returns a Range Object that represents only those type of cells we specify. For example, one can use the SpecialCells Method to return a Range Object that only contains formulae. In fact, we can, if we wish, even narrow it down further to have our Range Object (containing only formulae) to return only formulae with errors.

The syntax for the SpecialCells Method is;
expression.SpecialCells(Type, Value)

Where "expression" must be a Range Object. For example Range("A1:C100"), ActiveSheet.UsedRange etc.

Type=XlCellType and can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range. Note this XlCellType will include empty cells that have had any of cells default format changed.
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

These arguments cannot be added together to return more than one XlCellType.

Value=XlSpecialCellsValue and can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues

These arguments can be added together to return more than one XlSpecialCellsValue.

The SpecialCells Method can be used in a wide variety of situations when you only need to work with cells housing specific types of data. For example, the code below would return a Range Object representing all formulae on the active Worksheet.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

If we wanted, we could narrow this down further to only return a Range Object representative of all formulae that are returning numbers.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas,xlNumbers)

Once we have the specific Range Object type returned we can then work with only those cells. This can often be done in one line of code, or you may need to loop through the range. See examples below;

Sub ColorAllFormulae()

    ActiveSheet.UsedRange.SpecialCells _
     (xlCellTypeFormulas).Interior.ColorIndex = 6

End Sub

Sub NegativeAllNumberFormulae()
Dim rRange As Range, rCell As Range
   
    Set rRange = ActiveSheet.UsedRange.SpecialCells _
     (xlCellTypeFormulas, xlNumbers)
     
     For Each rCell In rRange
        rCell = rCell.Value * -1
     Next rCell
    
End Sub

Although I have used a loop on the second macro, so that all returned numbers are converted to their negative counterparts, we could make use of PasteSpecial to do so without looping and allow the formulae to remain in the cells. That is;

Sub NegativeAllNumberFormulae2()

   With Range("IV65536")
       .Value = -1
       .Copy
            ActiveSheet.UsedRange.SpecialCells _
                (xlCellTypeFormulas, xlNumbers).PasteSpecial _
                xlPasteValues, xlPasteSpecialOperationMultiply
        .Clear
   End With
    
End Sub

SpecialCells Gotcha!

If you are familiar with Excel and it's built in features, such as SpecialCells, you will know that when/if one specifies only a single cell (via Selection or Range) Excel will assume you wish to work with the entire Worksheet of cells. For example, the 2 macros below would both select ALL blank cells on a Worksheet.

Sub SelectAllBlanks()

    ActiveSheet.UsedRange.SpecialCells _
     (xlCellTypeBlanks).Select

End Sub

Sub SelectAllBlanks2()

    Range("A1").SpecialCells _
     (xlCellTypeBlanks).Select
    
End Sub

So, as you can see, specifying only a single cell Range can give unwanted results.

SpecialCells for Formulae & Constants

While we cannot specify more than one XlCellType (e.g. xlCellTypeConstants+xlCellTypeFormulas would fail) we can use the SpecialCells method to return only used cells housing numbers on a Worksheet (formulae & constants) and omit any cells containing text (formulae & constants).

Sub AllNummericCells()
Dim rCcells As Range, rFcells As Range
Dim rAcells As Range

    'Set variable to all used cells
    Set rAcells = ActiveSheet.UsedRange

    On Error Resume Next 'In case of no numeric formula or constants.

    'Set variable to all numeric constants
    Set rCcells = rAcells.SpecialCells(xlCellTypeConstants, xlNumbers)
    'Set variable to all numeric formulas
    Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas, xlNumbers)

    'Determine which type of numeric data (formulas, constants or none)
    If rCcells Is Nothing And rFcells Is Nothing Then
       MsgBox "You Worksheet contains no numbers"
       End
    ElseIf rCcells Is Nothing Then
       Set rAcells = rFcells 'formulas
    ElseIf rFcells Is Nothing Then
       Set rAcells = rCcells 'constants
    Else
       Set rAcells = Application.Union(rFcells, rCcells) 'Both
    End If
    On Error GoTo 0

    rAcells.Select
End Sub

You should take note of the On Error Resume Next statement in the above code. This is needed as when the SpecialCells Method condition cannot be met an error occurs. As you may, or may not know, a non valid Range Object returns the Nothing keyword. After Setting a Range variable to the SpecialCells Method we need to then check that we have been able to pass a Range Object to our Range variable. It is the If Statement (and 2 ElseIf) that checks this in the code above.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft