Cell related procedures in class Range (Excel VBA)

This page presents procedures from class Range related to the theme Cell: Cells, MergeCells, PivotCell, SetCellDataTypeFromCell and SpecialCells

Cells

Returns a Range object that represents the cells in the specified range.

The return value is a Range consisting of single cells, which allows to use the version of the Item with two parameters and lets For Each loops iterate over single cells. Because the default member of Range forwards calls with parameters to the Item property, you can specify the row and column index immediately after the Cells keyword instead of an explicit call to Item. Using Cells without an object qualifier is equivalent to ActiveSheet.Cells.

With Worksheets("Sheet1").Range("B2:Z100") 
   .Range(.Cells(1, 1), .Cells(5, 3)).Font.Italic = True
End With

MergeCells

True if the range contains merged cells.

When you select a range that contains merged cells, the resulting selection may be different from the intended selection. Use the Address property to check the address of the selected range.

ActiveCell.MergeCells = True

PivotCell

Returns a PivotCell object that represents a cell in a PivotTable report.

Dim pvtlcelPivotCell As PivotCell
Set pvtlcelPivotCell = ActiveCell.PivotCell

SetCellDataTypeFromCell

Creates another instance of a Linked data type, such as Stocks or Geography, that exists in another cell. The new instance will be linked to the data source in the same way as the original, so it will refresh from the service if you call the Workbook.RefreshAll method.

SetCellDataTypeFromCell (SourceCell)

SourceCell: The range from which you want to copy the Linked data type. If the range has more than one cell in it, only the upper-left cell will be used.

ActiveCell.SetCellDataTypeFromCell SourceCell:=Range("A1")

SpecialCells

Returns a Range object that represents all the cells that match the specified type and value.

Use the XlSpecialCellsValue enumeration to specify cells with a particular type of value to include in the result.

SpecialCells (Type, Value)

Dim rngSpecialCells As Range
Set rngSpecialCells = ActiveCell.SpecialCells(Type:=xlCellTypeAllFormatConditions)

Arguments

The following argument is required

Type (XlCellType) - The cells to include.

Here you can find possible values for XlCellType.

The following argument is optional

Value (Variant) - If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type.