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


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, _ 

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

objConstantCells = Selection.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 

The following line clears all constants from a worksheet

objWorksheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 

Selecting all non blank cells on a sheet

Public Sub AllNonBlank() 
                                                    Value:=xlSpecialCellsValue.xlErrors + _
                                                           xlSpecialCellsValue.xlLogical + _
                                                           xlSpecialCellsValue.xlNumbers + _
                                                           xlSpecialCellsValue.xlTextValues), _
                     ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _
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

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