Class Range (Excel VBA)

The class Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.

Dim rng as Range
Set rng = ActiveCell

For Each

Here is an example of processing the Range items in a collection.

Dim rng As Range: Set rng = 
Dim rngArea As Range
For Each rngArea In rng.Areas
	With rngArea
		
	End With
Next rngArea

Methods

Activate - Activates a single cell, which must be inside the current selection. To select a range of cells, use the Select method.

Worksheets("Sheet1").Activate 
Range("A1:C3").Select 
Range("B2").Activate

AutoFilter - Filters a list by using the AutoFilter.

Worksheets("Sheet1").Range("A1").AutoFilter _
 Field:=1, _
 Criteria1:="Otis", _
 VisibleDropDown:=False

Consolidate - Consolidates data from multiple ranges on multiple worksheets into a single range on a single worksheet.

Worksheets("Sheet1").Range("A1").Consolidate _ 
 Sources:=Array("Sheet2!R1C1:R37C6", "Sheet3!R1C1:R37C6"), _ 
 Function:=xlSum

CopyFromRecordset - Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails.

Dim lngCopyFromRecordset As Long
lngCopyFromRecordset = ActiveCell.CopyFromRecordset(Data:=)

CopyPicture - Copies the selected object to the Clipboard as a picture.

ActiveCell.CopyPicture

ExportAsFixedFormat - Exports to a file of the specified format.

ActiveCell.ExportAsFixedFormat Type:=xlTypePDF

FillDown - Fills down from the top cell or cells in the specified range to the bottom of the range. The contents and formatting of the cell or cells in the top row of a range are copied into the rest of the rows in the range.

Worksheets("Sheet1").Range("A1:A10").FillDown

FillRight - Fills right from the leftmost cell or cells in the specified range. The contents and formatting of the cell or cells in the leftmost column of a range are copied into the rest of the columns in the range.

Worksheets("Sheet1").Range("A1:M1").FillRight

Find - Finds specific information in a range.

Dim strWhat As String: strWhat = 
Dim rngFind As Range
Set rngFind = Worksheets("Sheet1").Range("A1:A10").Find(What:=strWhat)

Merge - Creates a merged cell from the specified Range object.

ActiveCell.Merge

PasteSpecial - Pastes a Range object that has been copied into the specified range.

With Worksheets("Sheet1") 
 .Range("C1:C5").Copy 
 .Range("D1:D5").PasteSpecial _ 
  Operation:=xlPasteSpecialOperationAdd 
End With

PrintOut - Prints the object.

ActiveCell.PrintOut

Select - Selects the object.

ActiveCell.Select

Subtotal - Creates subtotals for the range (or the current region, if the range is a single cell).

Worksheets("Sheet1").Activate 
Selection.Subtotal GroupBy:=1, Function:=xlSum, _ 
 TotalList:=Array(2, 3)

AddComment - Adds a comment to the range.

AddCommentThreaded - Adds a new modern threaded comment to the range if no comment already exists.

AdvancedFilter - Filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used.

AllocateChanges - Performs a writeback operation for all edited cells in a range based on an OLAP data source.

ApplyNames - Applies names to the cells in the specified range.

ApplyOutlineStyles - Applies outlining styles to the specified range.

AutoComplete - Returns an AutoComplete match from the list. If there's no AutoComplete match or if more than one entry in the list matches the string to complete, this method returns an empty string.

AutoFill - Performs an autofill on the cells in the specified range.

AutoFit - Changes the width of the columns in the range or the height of the rows in the range to achieve the best fit.

AutoOutline - Automatically creates an outline for the specified range. If the range is a single cell, Microsoft Excel creates an outline for the entire sheet. The new outline replaces any existing outline.

BorderAround - Adds a border to a range and sets the Color, LineStyle, and Weight properties of the Border border for the new border. Variant.

Calculate - Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the table in the Remarks section.

CalculateRowMajorOrder - Calculates a specified range of cells.

CheckSpelling - Checks the spelling of an object.

Clear - Clears the entire object.

ClearComments - Clears all cell comments from the specified range.

ClearContents - Clears formulas and values from the range.

ClearFormats - Clears the formatting of the object.

ClearHyperlinks - Removes all hyperlinks from the specified range.

ClearNotes - Clears notes and sound notes from all the cells in the specified range.

ClearOutline - Clears the outline for the specified range.

ColumnDifferences - Returns a Range object that represents all the cells whose contents are different from the comparison cell in each column.

ConvertToLinkedDataType - Attempts to convert all the cells in the range to a Linked data type such as Stocks or Geography.

Copy - Copies the range to the specified range or to the Clipboard.

CreateNames - Creates names in the specified range, based on text labels in the sheet.

Cut - Cuts the object to the Clipboard or pastes it into a specified destination.

DataSeries - Creates a data series in the specified range.

DataTypeToText - If any of the cells in the range are a Linked data type, such as Stocks or Geography, this call will convert their values to text.

Delete - Deletes the object.

Dirty - Designates a range to be recalculated when the next recalculation occurs.

DiscardChanges - Discards all changes in the edited cells of the range.

FillLeft - Fills left from the rightmost cell or cells in the specified range. The contents and formatting of the cell or cells in the rightmost column of a range are copied into the rest of the columns in the range.

FillUp - Fills up from the bottom cell or cells in the specified range to the top of the range. The contents and formatting of the cell or cells in the bottom row of a range are copied into the rest of the rows in the range.

FindNext - Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. This does not affect the selection or the active cell.

FindPrevious - Continues a search that was begun with the Find method. Finds the previous cell that matches those same conditions and returns a Range object that represents that cell. Doesn't affect the selection or the active cell.

FlashFill - True indicates that the Excel Flash Fill feature has been enabled and is active.

FunctionWizard - Starts the Function Wizard for the upper-left cell of the range.

Group - When the Range object represents a single cell in a PivotTable field's data range, the Group method performs numeric or date-based grouping in that field.

Insert - Inserts a cell or a range of cells into the worksheet or macro sheet and shifts other cells away to make space.

InsertIndent - Adds an indent to the specified range.

Justify - Rearranges the text in a range so that it fills the range evenly.

ListNames - Pastes a list of all nonhidden names onto the worksheet, beginning with the first cell in the range.

NavigateArrow - Navigates a tracer arrow for the specified range to the precedent, dependent, or error-causing cell or cells. Selects the precedent, dependent, or error cells and returns a Range object that represents the new selection. This method causes an error if it's applied to a cell without visible tracer arrows.

Parse - Parses a range of data and breaks it into multiple cells. Distributes the contents of the range to fill several adjacent columns; the range can be no more than one column wide.

PrintPreview - Shows a preview of the object as it would look when printed.

RefreshLinkedDataType -

RemoveDuplicates - Removes duplicate values from a range of values.

RemoveSubtotal - Removes subtotals from a list.

Replace - Returns a Boolean indicating characters in cells within the specified range. Using this method doesn't change either the selection or the active cell.

RowDifferences - Returns a Range object that represents all the cells whose contents are different from those of the comparison cell in each row.

Run - Runs the Microsoft Excel macro at this location. The range must be on a macro sheet.

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.

SetPhonetic - Creates Phonetic objects for all the cells in the specified range.

Show - Scrolls through the contents of the active window to move the range into view. The range must consist of a single cell in the active document.

ShowCard - For a cell containing a Linked data type, such as Stocks or Geography, this method causes a card to appear that shows details about the cell (that is, the same card that the user can view by choosing the cell icon).

ShowDependents - Draws tracer arrows to the direct dependents of the range.

ShowErrors - Draws tracer arrows through the precedents tree to the cell that's the source of the error, and returns the range that contains that cell.

ShowPrecedents - Draws tracer arrows to the direct precedents of the range.

Sort - Sorts a range of values.

SortSpecial - Uses East Asian sorting methods to sort the range, a PivotTable report, or uses the method for the active region if the range contains only one cell. For example, Japanese sorts in the order of the Kana syllabary.

Speak - Causes the cells of the range to be spoken in row order or column order.

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

Table - Creates a data table based on input values and formulas that you define on a worksheet.

TextToColumns - Parses a column of cells that contain text into several columns.

Ungroup - Promotes a range in an outline (that is, decreases its outline level). The specified range must be a row or column, or a range of rows or columns. If the range is in a PivotTable report, this method ungroups the items contained in the range.

UnMerge - Separates a merged area into individual cells.

Properties

AddIndent returns or sets a value that indicates if text is automatically indented when the text alignment in a cell is set to equal distribution (either horizontally or vertically).

Address returns a String value that represents the range reference in the language of the macro.

AddressLocal returns the range reference for the specified range in the language of the user.

AllowEdit returns a Boolean value that indicates if the range can be edited on a protected worksheet.

Areas returns an Areas collection that represents all the ranges in a multiple-area selection.

Borders returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format).

Cells returns a Range object that represents the cells in the specified range.

Characters returns a Characters object that represents a range of characters within the object text. You can use the Characters object to format characters within a text string.

Column returns the number of the first column in the first area in the specified range.

Columns returns a Range object that represents the columns in the specified range.

ColumnWidth returns or sets the width of all columns in the specified range. Read/write Double.

Comment returns a Comment object that represents the comment associated with the cell in the upper-left corner of the range.

CommentThreaded returns a CommentThreaded object that represents the threaded comment associated with the cell in the upper-left corner of the range.

Count returns a Long value that represents the number of objects in the collection.

CountLarge returns a value that represents the number of objects in the collection.

CurrentArray if the specified cell is part of an array, returns a Range object that represents the entire array.

CurrentRegion returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns.

Dependents returns a Range object that represents the range containing all the dependents of a cell. This can be a multiple selection (a union of Range objects) if there's more than one dependent.

DirectDependents returns a Range object that represents the range containing all the direct dependents of a cell. This can be a multiple selection (a union of Range objects) if there's more than one dependent.

DirectPrecedents returns a Range object that represents the range containing all the direct precedents of a cell. This can be a multiple selection (a union of Range objects) if there's more than one precedent.

DisplayFormat returns a DisplayFormat object that represents the display settings for the specified range.

End returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW.

EntireColumn returns a Range object that represents the entire column (or columns) that contains the specified range.

EntireRow returns a Range object that represents the entire row (or rows) that contains the specified range.

Errors allows the user to access error checking options.

Font returns a Font object that represents the font of the specified object.

FormatConditions returns a FormatConditions collection that represents all the conditional formats for the specified range.

Formula returns or sets a Variant value that represents the string's implicitly intersecting formula in A1-style notation.

Formula2 returns or sets a value that represents the object's formula in A1-style array notation.

Formula2Local this is the Formula2 Variant of Range.FormulaLocal. See Range.Formula2 for more detail.

Formula2R1C1 this is the Formula2 Variant of Range.FormulaR1C1. See Range.Formula2 and Range.FormulaR1C1 for more detail.

Formula2R1C1Local this is the Formula2 Variant of Range.FormulaR1C1Local. See Range.Formula2 and Range.FormulaR1C1Local for more detail.

FormulaArray returns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returns null.

FormulaHidden returns or sets a value that indicates if the formula will be hidden when the worksheet is protected.

FormulaLocal returns or sets the formula for the object, using A1-style references in the language of the user.

FormulaR1C1 returns or sets the formula for the object, using R1C1-style notation in the language of the macro.

FormulaR1C1Local returns or sets the formula for the object, using R1C1-style notation in the language of the user.

HasArray true if the specified cell is part of an array formula. Read-only Variant.

HasFormula true if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise. Read-only Variant.

HasRichDataType true if all cells in the range contain a Rich data type. False if none of the cells in the range contains a Rich data type; otherwise, null. Read-only Variant.

HasSpill true if all of the cells in the range are part of a spilled range; False if none of the cells in the range are part of a spilled range; null otherwise. Read-only Variant.

Height returns a Double value that represents the height, in points, of the range. Read-only.

Hidden returns or sets a Variant value that indicates if the rows or columns are hidden.

HorizontalAlignment returns or sets a value that represents the horizontal alignment for the specified object.

Hyperlinks returns a Hyperlinks collection that represents the hyperlinks for the range.

ID returns or sets a String value that represents the identifying label for the specified cell when the page is saved as a webpage.

IndentLevel returns or sets a value that represents the indent level for the cell or range. Can be an integer from 0 to 15.

Interior returns an Interior object that represents the interior of the specified object.

Item returns a Range object that represents a range at an offset to the specified range.

Left returns a Variant value that represents the distance, in points, from the left edge of column A to the left edge of the range.

LinkedDataTypeState returns information about the state of any Linked data types, such as Stocks or Geography, in the range. Possible values are from the XlLinkedDataTypeState enumeration. Read-only.

ListHeaderRows returns the number of header rows for the specified range.

ListObject returns a ListObject object for the Range object.

LocationInTable returns a constant that describes the part of the PivotTable report that contains the upper-left corner of the specified range. Can be one of the following XlLocationInTable constants.

Locked returns or sets a Variant value that indicates if the boolean is locked.

MDX returns the MDX name for the specified Range object.

MergeArea returns a Range object that represents the merged range containing the specified cell. If the specified cell isn't in a merged range, this property returns the specified cell.

MergeCells true if the range contains merged cells.

Name returns or sets a Variant value that represents the name of the string.

Next returns a Range object that represents the next cell.

NumberFormat returns or sets a value that represents the format code for the object.

NumberFormatLocal returns or sets a value that represents the format code for the object as a string in the language of the user.

Offset returns a Range object that represents a range that's offset from the specified range.

Orientation returns or sets a value that represents the text orientation.

OutlineLevel returns or sets the current outline level of the specified row or column.

PageBreak returns or sets the location of a page break. Can be one of the following XlPageBreak constants: xlPageBreakAutomatic, xlPageBreakManual, or xlPageBreakNone.

Parent returns the parent worksheet for the specified worksheet. Read-only.

Phonetic returns the Phonetic object, which contains information about a specific phonetic text string in a cell.

Phonetics returns the Phonetics collection of the range.

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

PivotField returns a PivotField object that represents the PivotTable field containing the upper-left corner of the specified range.

PivotItem returns a PivotItem object that represents the PivotTable item containing the upper-left corner of the specified range.

PivotTable returns a PivotTable object that represents the PivotTable report containing the upper-left corner of the specified range.

Precedents returns a Range object that represents all the precedents of a cell. This can be a multiple selection (a union of Range objects) if there's more than one precedent.

PrefixCharacter returns the prefix character for the cell.

Previous returns a Range object that represents the previous cell.

QueryTable returns a QueryTable object that represents the query table that intersects the specified Range object.

Range returns a Range object that represents a cell or a range of cells.

ReadingOrder returns or sets the reading order for the specified object. Can be one of the following XlReadingOrder constants: xlRTL (right-to-left), xlLTR (left-to-right), or xlContext.

Resize resizes the specified range. Returns a Range object that represents the resized range.

Row returns the number of the first row of the first area in the range.

RowHeight returns or sets the height of the first row in the range specified, measured in points. Read/write Double.

Rows returns a Range object that represents the rows in the specified range.

SavedAsArray true if all of the cells in the range would be saved to file as an array formula; False if none of the cells in the range would be saved to file as a legacy array formula; null otherwise.

ServerActions specifies the actions that can be performed on the SharePoint server for a Range object.

ShowDetail true if the outline is expanded for the specified range (so that the detail of the column or row is visible). The specified range must be a single summary column or row in an outline. Read/write Variant.

ShrinkToFit returns or sets a value that indicates if text automatically shrinks to fit in the available column width.

SoundNote this property should not be used. Sound notes have been removed from Microsoft Excel.

SparklineGroups returns a SparklineGroups object that represents an existing group of sparklines from the specified range.

SpillingToRange

SpillParent if a cell is a member of a spill, returns the cell containing the formula responsible. Otherwise, an error is returned.

Style returns or sets a Variant value containing a Style style that represents the style of the specified range.

Summary true if the range is an outlining summary row or column. The range should be a row or a column.

Text returns the formatted text for the specified string. Read-only String.

Top returns a Variant value that represents the distance, in points, from the top edge of row 1 to the top edge of the range.

UseStandardHeight true if the row height of the Range object equals the standard height of the sheet. Returns Null if the range contains more than one row and the rows aren't all the same height.

UseStandardWidth true if the column width of the Range object equals the standard width of the sheet. Returns null if the range contains more than one column and the columns aren't all the same width.

Validation returns the Validation object that represents data validation for the specified range.

Value returns or sets a Variant value that represents the value of the specified range.

Value2 returns or sets the cell value.

VerticalAlignment returns or sets a value that represents the vertical alignment of the specified object.

Width returns a Double value that represents the width of a range in points. Read-only.

Worksheet returns a Worksheet object that represents the worksheet containing the specified range.

WrapText returns or sets a Variant value that indicates if Microsoft Excel wraps the text in the string.

XPath returns an XPath object that represents the XPath of the element mapped to the specified Range object. The context of the range determines whether the action succeeds or returns an empty object.

Related Classes

Actions - A collection of all Action objects for the specified series.

Areas - A collection of the areas, or contiguous blocks of cells, within a selection.

Border - Represents the border of an object.

Borders - A collection of four Border objects that represent the four borders of a Range object or Style object.

Characters - Represents characters in an object that contains text.

Comment - Represents a cell comment.

CommentThreaded - Represents a cell's threaded comment. This object can represent both a top-level comment or its replies.

DisplayFormat - Represents the display settings for an associated Range object. Read-only.

Errors - Represents the various spreadsheet errors for a range.

Font - Contains the font attributes (font name, font size, color, and so on) for an object.

FormatConditions - Represents the collection of conditional formats for a single range.

Hyperlinks - Represents the collection of hyperlinks for a worksheet or range.

Interior - Represents the interior of an object.

ListObject - Represents a list object in the ListObjects collection.

Name - Represents a defined name for a range of cells. Names can be either built-in names—such as Database, Print_Area, and Auto_Open—or custom names.

Phonetic - Contains information about a specific phonetic text string in a cell.

Phonetics - A collection of all the Phonetic objects in the specified range.

PivotCell - Represents a cell in a PivotTable report.

PivotField - Represents a field in a PivotTable report.

PivotItem - Represents an item in a PivotTable field.

PivotTable - Represents a PivotTable report on a worksheet.

QueryTable - Represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database.

SoundNote -

SparklineGroups - Represents a collection of sparkline groups.

Style - Represents a style description for a range.

Validation - Represents data validation for a worksheet range.

Worksheet - Represents a worksheet.

XPath - Represents an XPath that has been mapped to a Range or ListColumn object.