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


BUG - More than 16,385 rows

There seems to be a problem with the SpecialCells method when you work with a range that contains more than 16,385 rows.
This following code works fine

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

This following code does not works fine and will select the whole workbook.

Sub DoesNotWork 
   Range("A1:A16386").SpecialCells(xlCellType.xlCellTypeFormulas, xlSpecialCellsValue.xlErrors).Select
End Sub



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext