Go To Special with VBA

You can use Excel VBA to quickly find and select all cells that contain a specific type of data, just like using the Go To Specials command. Most Go To Specials command use the SpecialCells method, if you want learn other select range method, read this article: Select a Range with Excel VBA.

Comments

Select the cells that contain comments in entire sheet:

Cells.SpecialCells(xlCellTypeComments).Select

Select the cells that contain comments in Range A1:E20:

Range("A1:E20").SpecialCells(xlCellTypeComments).Select

Constants

The xlCellTypeConstants argument values can be added together to return more than one type:

Select the cells that contain constants in entire sheet:

Cells.SpecialCells(xlCellTypeConstants, 23).Select

=

Cells.SpecialCells(xlCellTypeConstants).Select

Select the cells that contain text constants in entire sheet:

Cells.SpecialCells(xlCellTypeConstants, 2).Select

Select the cells that contain numeric constants in the used range:

ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 1).Select

Select the cells that contain logical constants in entire sheet:

Cells.SpecialCells(xlCellTypeConstants, 4).Select

Select the cells that contain error constants in entire sheet:

Cells.SpecialCells(xlCellTypeConstants, 16).Select

Formulas

The xlCellTypeFormulas argument values can be added together to return more than one type:

Select the cells that contain formulas in entire sheet:

Cells.SpecialCells(xlCellTypeFormulas, 23).Select

=

Cells.SpecialCells(xlCellTypeFormulas).Select

Select the cells that contain formulas with text values in entire sheet:

Cells.SpecialCells(xlCellTypeFormulas, 2).Select

Select the cells that contain formulas with numeric values in the used range:

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 1).Select

Select the cells that contain formulas with logical values in entire sheet:

Cells.SpecialCells(xlCellTypeFormulas, 4).Select

Select the cells that contain formulas with error values in entire sheet:

Cells.SpecialCells(xlCellTypeFormulas, 16).Select

Blanks

Select the blank cells in entire sheet:

Cells.SpecialCells(xlCellTypeBlanks).Select

Select the blank cells in Range B2:J20:

Range("B2:J20").SpecialCells(xlCellTypeBlanks).Select

Current region

Select a rectangular range of cells around cell A1:

Range("A1").CurrentRegion.Select

Current array

Select an entire array if the active cell is contained in an array:

ActiveCell.CurrentArray.Select

Objects

Select all graphical objects, including charts and buttons on the active sheet:

ActiveSheet.DrawingObjects.Select

Row differences

Selects the values that are unlike A1 in row one:

Rows(1).RowDifferences(Range("A1")).Select

Column differences

Selects the values that are unlike A1 in column one:

Columns("A").ColumnDifferences(Range("A1")).Select

Precedents

Selects the precedents of cell A1:

Range("A1").Precedents.Select

Selects the direct precedents of cell A1:

Range("A1").DirectPrecedents.Select

Dependents

Selects the dependents of cell A1:

Range("A1").Dependents.Select

Selects the direct dependents of cell A1:

Range("A1").DirectDependents.Select

Last cell

Selects the last cell in the used range:

Cells.SpecialCells(xlCellTypeLastCell).Select

Visible cells only

Selects the cells that are visible in used range that crosses hidden rows or columns:

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Select

Conditional formats

Selects all cells that have conditional formats applied:

Cells.SpecialCells(xlCellTypeAllFormatConditions).Select

Selects the cells that have the same conditional formats as cell A1.

Range("A1").SpecialCells(xlCellTypeSameFormatConditions).Select

Data validation

Selects all cells that have data validation applied:

Cells.SpecialCells(xlCellTypeAllValidation).Select

Selects the cells that have the same data validation as cell A1:

Range("A1").SpecialCells(xlCellTypeSameValidation).Select