What are Special Cells in Excel VBA

The SpecialCells Method in Excel VBA
The Special Cells in Excel are nothing but the Cells having the Formuls,Comments,Conditional Formatting and etc...
One of the most useful Methods in Excel is the SpecialCells Method. When use this method, it returns a Range Object that represents only those types of cells we specified.

The SpecialCells Method can be used in a wide variety of situations when you only need to work with cells having specific types of data.

For example, one can use the SpecialCells Method to return a Range Object that only contains only Visible Cells or Formulae.

Syntax of SpecialCells Method:
Expression.SpecialCells(Type, Value)

Where "Expression" must be a Range Object like 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 having 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 representing 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 as in below Examples:

Sub Color_All_Formulae_Cells()

    ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 10

End Sub

Sub Negative_All_Number_Formulae()

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

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------