Special Cells Method

Select (Work With) Specific Cell Types in Excel

Got any Excel/VBA Questions? Excel Help.

The Special Cells Method in Excel VBA

One of the most beneficial Methods in Excel (in my experience) is the Special Cells Method. When used, it returns a Range Object that represents only those type of cells we specify. For example, one can use the Special Cells 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 Paste Special 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.

Gallery

stars (0 Reviews)

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

Website designed by Astrava Solutions Ltd.