Using SpecialCells in Excel VBA

The SpecialCells method of the Range class returns a new range that represents all the cells that match its arguments. This allows you to process a subset of the cells in a range, e.g. the cells that have comments, or those that are empty, or whose value is determined by a formula. The table below shows which subsets of the cells in the range or worksheet you may get:

 As an example, the code below returns a range containing all cells that contain numeric constants (values, not formulas - e.g. 20.4). This page discusses the details on how to use this important methods. First I will present usefull info on what will be returned and how to prevent errors in your code. After that, I will walk through the different types of cells you can obtain with it. The examples assume data in the active sheet as in the image on the right.

Dim rng As Range: Set rng = Application.Range("B2:C4")
Dim rngSpecialCells As Range
Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers)
Debug.Print rngSpecialCells.Address

The above code returns $B$4 as the range containing numbers

How to use SpecialCells - some peculiarities

When using SpecialCells you will run into a number of peculiarities which I will discuss here first.

SpecialCells in a specified multicell range or the used range of the worksheet

Excel often, and also in the case of SpecialCells, assumes you want the used range of the worksheet if your specified range only contains a single cell. As an example, the below code returns everything in the UsedRange of the active worksheet:$B$4:$B$5,$A$4:$A$6,$C$5:$C$6,$A$8

Dim rng As Range: Set rng = ActiveSheet.Cells(1)
Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers)

If in the first example we specified as the range to work on as "B2:C3", no cells would have been found in that range. Summerizing:

Handling error 1004: No cells were found

If we would ask for cells containing logical values (TRUE or FALSE) no cells would have been returned. Instead, without error handling, the user would get the error message on the right. The code below shows a way you could prevent this error from occurring. If no cells are found the rngSpecialCells keeps its original Nothing value and here this routes to the Exit. Depending on your requirements you may want a more sophisticated error handling.

On Error Resume Next
Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeConstants, Value:=xlLogical)
If rngSpecialCells Is Nothing Then
   Exit Sub
End If
Debug.Print rngSpecialCells.Address

Count

To obtain the number of cells, given a returned rngSpecialCells - not Nothing:

rngSpecialCells.Count

Looping over the cells

Along the same path, iterating over the cells in the range:

Dim cel As Range
For Each cel In rngSpecialCells.Cells
  
Next cel

Different types of cells you can obtain

In this section I show the different types of cells you can obtain using SpecialCells

Get Last cell in worksheet using SpecialCells

Quite often, we need to know the last cell of the worksheet, which is achieved using the following code.

Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeLastCell)

Empty cells

To get the cells with no content (data constant or via formula) use SpecialCells with Type xlCellTypeBlanks. In the below example this returns $A$2,$C$2

Dim rng As Range: Set rng = Application.Range("A2:C5")
Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeBlanks)

Finding cells that contain data constants

The first example on this page showed the code that returns a range containing all cells that contain numeric constants. If you want only cells with only logical (TRUE/FALSE) values use Value:=xlLogical, for text only use the code below.

    
Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeConstants, Value:=xlTextValues)

If you want to check for cells containing data of any type you can leave out the Value argument:

Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeConstants)

Finding cells that contain formulas

The code below returns all formulas that result in errors. Similar to the previous sections you can also restrict the result to cells that get numbers, text or logical values by setting the corresponding Value argument to xlNumbers, xlxlTextValues or xlLogical respectively

Set rngSpecialCells = rngColumn.SpecialCells(Type:=xlCellTypeFormulas, Value:=xlErrors)

Cells that have Comments

The following code - created fully using the Code VBA add-in - prints all the Comment on the active worksheet to the Immediate window.

Sub ListCommentsOnActiveSheet()
On Error Resume Next
Dim ws As Worksheet: Set ws = Application.ActiveSheet
Dim rng As Range: Set rng = ws.Cells(1)
Dim rngSpecialCells As Range
Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeComments)
Dim cmm As Comment: Set cmm = rngSpecialCells.Comment
Debug.Print cmm.Text
End Sub

If you want to exclude cells - in rows or columns - that are hidden:

Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeVisible)

Find Cells with Conditional Formats

To find Cells having any Conditional Formats use the code below.

    
Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeAllFormatConditions)

If you want to find Cells having the same Conditional Formats as a specific cell - here with the more meaningful name of the single cell range rngSameAsThisCell:

Set rngSpecialCells = rngSameAsThisCell.SpecialCells(Type:=xlCellTypeSameFormatConditions)

Find Cells with Validation

To find Cells having any Validation use the code below.

Set rngSpecialCells = rng.SpecialCells(Type:=xlCellTypeAllValidation)

If you want to find Cells having the same Conditional Formats as a specific cell - here with the more meaningful name of the single cell range rngSameAsThisCell:

Set rngSpecialCells = rngSameAsThisCell.SpecialCells(Type:=xlCellTypeSameValidation)