Special Cells complete guide Excel Add-in with code example

SpecialCells

Method derived from two arguments Type and Value and returns a range object. Special Cells defines filter criteria to select range, the filter of special cells gets determined by its type derived from XlCellType enumeration having following constants. If not cell found to given type a message will be returned “No cell were found“.

Syntax

Range.SpecialCells(Type, Value)

Parameters

1. Type: determines the type of cells to be extracted from given range or selection. It is a mandatory argument.
2. Value: this is an optional argument, constant xlCellTypeConstants or xlCellTypeFormulas passed as Value argument would determine the inclusion criteria of selection type.

Constants

xlCellTypeAllFormatConditions: all conditional formatted cells will be returned as range. Follow given image where we have only 1 conditional formatting available pointing to “A7:B7“.

Code example

Public Sub AllFormatConditionsSpecialCells()
    'object to get current selection
    Dim oRange As range
    Set oRange = Selection
    
    'Object to hold result
    Dim filterResult As range
    Set filterResult = oRange.SpecialCells(XlCellType.xlCellTypeAllFormatConditions)
    
    'Iterate each conditional formatted cell
    Dim oCell As range
    For Each oCell In filterResult
        'Print values
        Debug.Print oCell.Value
    Next oCell
    
    'Cleanup
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
    If Not filterResult Is Nothing Then
        Set filterResult = Nothing
    End If
    If Not oCell Is Nothing Then
        Set oCell = Nothing
    End If
End Sub

Output

Prod-C
1277

xlCellTypeAllValidation: returns all cells having validation applied on. Following example shows validation highlighted:

Code example

Public Sub AllValidtionsSpecialCells()
    'object to get current selection
    Dim oRange As range
    Set oRange = Selection
    
    'Object to hold result
    Dim filterResult As range
    Set filterResult = oRange.SpecialCells(XlCellType.xlCellTypeAllValidation)
    
    'Iterate each conditional formatted cell
    Dim oCell As range
    For Each oCell In filterResult
        'Print values
        Debug.Print "Row Number: " & oCell.Row & " Column Number: "; oCell.Column
    Next oCell
    
    'Cleanup
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
    If Not filterResult Is Nothing Then
        Set filterResult = Nothing
    End If
    If Not oCell Is Nothing Then
        Set oCell = Nothing
    End If
End Sub

Output

Row Number: 2 Column Number: 1
Row Number: 2 Column Number: 2
Row Number: 3 Column Number: 1
Row Number: 3 Column Number: 2
Row Number: 4 Column Number: 1
Row Number: 4 Column Number: 2
Row Number: 5 Column Number: 1
Row Number: 5 Column Number: 2
Row Number: 6 Column Number: 1
Row Number: 6 Column Number: 2
Row Number: 7 Column Number: 1
Row Number: 7 Column Number: 2
Row Number: 8 Column Number: 1
Row Number: 8 Column Number: 2
Row Number: 9 Column Number: 1
Row Number: 9 Column Number: 2
Row Number: 10 Column Number: 1
Row Number: 10 Column Number: 2
Row Number: 11 Column Number: 1
Row Number: 11 Column Number: 2
Row Number: 12 Column Number: 1
Row Number: 12 Column Number: 2
Row Number: 13 Column Number: 1
Row Number: 13 Column Number: 2
Row Number: 14 Column Number: 1
Row Number: 14 Column Number: 2

xlCellTypeBlanks: returns all blank cells as range which can be further used for manipulation.

Code example

Set filterResult = oRange.SpecialCells(XlCellType.xlCellTypeBlanks)

xlCellTypeComments: returns all comments from selected cells as a result.

Code example

oRange.SpecialCells(XlCellType.xlCellTypeComments)

xlCellTypeConstants: returns only cells contains constants, formula driven cells would be ignored in the result.

Code example

oRange.SpecialCells(XlCellType.xlCellTypeConstants)

xlCellTypeFormulas: return only cells having formulas as part for the result. Given code scan for all formula driven cells with combination of cells having Error.

Code example

Set filterResult = oRange.SpecialCells(XlCellType.xlCellTypeFormulas, xlErrors)

xlCellTypeLastCell: returns the last cell reference as part of the result.

Code example

oRange.SpecialCells(XlCellType.xlCellTypeLastCell)

xlCellTypeSameFormatConditions: returns cells having same formatting conditions.

Code example

oRange.SpecialCells(XlCellType.xlCellTypeSameFormatConditions)

xlCellTypeSameValidation: returns cells having same validations applied to as part of the result.

Code example

oRange.SpecialCells(XlCellType.xlCellTypeSameValidation)

xlCellTypeVisible: widely used constants returns visible cells. Mostly used to retrieve filtered result excluding errors. Following example copies filtered result to another location.

Source data

Code example

Public Sub VisibleSpecialCells()
    'object to get current selection
    Dim oRange As range
    Set oRange = Selection
    
    'Object to hold result
    Dim filterResult As range
    Set filterResult = oRange.SpecialCells(XlCellType.xlCellTypeVisible, xlNumbers + xlFormula)
    
    'copy paste filtered data
    filterResult.Copy range("D1")
    
    'Cleanup
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
    If Not filterResult Is Nothing Then
        Set filterResult = Nothing
    End If
End Sub

Output

Next >> Create Custom Task Panel or Side Panel Excel Add-in example

Share on Facebook

Tweet