SpecialCells

Range.SpecialCells Method

This method returns a range that represents all the cells that match a particular criteria

Range.SpecialCells(Type, Value) 

Type - The type of cells to select from the xlCellType enumeration.
Value - This is an additional argument that is required when Type is xlCellTypeConstants or xlCellTypeFormulas.
The default is to select all constants and formulas.

objBlankCells = Selection.SpecialCells(Type:=xlCellType.xlCellTypeBlanks) 

The following line selects the last "used" cell on the worksheet

ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeLastCell) 

xlCellTypeConstants or xlCellTypeFormulas.

Using this method will generate an error if there are no cells currently selected.
The following line creates a subset range containing all the cells that contain formulas.

objFormulaCells = Selection.SpecialCells(Type:=xlCellType.xlCellTypeFormulas, _ 
                                         Value:=xlSpecialCellsValue.xlNumbers) 

The following line creates a subset range containing all the cells that contain constants

objConstantCells = Selection.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                                          Value:=xlSpecialCellsValue.xlNumbers) 

The following line clears all constants from a worksheet

objWorksheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                                Value:=xlSpecialCellsValue.xlNumbers).ClearContents 

Selecting all non blank cells on a sheet

Public Sub AllNonBlank() 
   Application.Union(ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeFormulas, 
                                                    Value:=xlSpecialCellsValue.xlErrors + _ 
                                                           xlSpecialCellsValue.xlLogical + _ 
                                                           xlSpecialCellsValue.xlNumbers + _ 
                                                           xlSpecialCellsValue.xlTextValues), _ 
                     ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                                                    Value:=23)).Select 
End Sub 

Selecting all cells in a range that contain an error

This will select any cells that contain: #CALC!, #DIV/0, #FIELD!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #SPILL!, #VALUE!.

Public Sub CellsWithErrors 
   Range("A1:A16385").SpecialCells(xlCellType.xlCellTypeFormulas, xlSpecialCellsValue.xlErrors).Select 
End Sub