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.

The main procedures of class Range are Activate, Copy, Delete and Select

Set

To use a Range class variable it first needs to be instantiated, for example

Dim rng as Range
Set rng = ActiveCell

The following procedures can be used to set variables of type Range: Areas, AboveAverage.AppliesTo, AllowEditRange.Range, Application.ActiveCell, Application.Cells, Application.Columns, Application.Intersect, Application.Range, Application.Rows, Application.Union, Application.PreviousSelections, Application.ThisCell, Areas.Item, AutoFilter.Range, Axis.CategoryNames, ChartObject.BottomRightCell, ChartObject.TopLeftCell, ColorScale.AppliesTo, Databar.AppliesTo, DisplayFormat.NumberFormat, DisplayFormat.AddIndent, DisplayFormat.HorizontalAlignment, DisplayFormat.Orientation, DisplayFormat.VerticalAlignment, DisplayFormat.FormulaHidden, DisplayFormat.IndentLevel, DisplayFormat.MergeCells, DisplayFormat.NumberFormatLocal, DisplayFormat.ShrinkToFit, FormatCondition.AppliesTo, HPageBreak.Location, Hyperlink.Range, IconSetCondition.AppliesTo, ListColumn.Range, ListColumn.DataBodyRange, ListColumn.Total, ListObject.Range, ListObject.DataBodyRange, ListObject.HeaderRowRange, ListObject.InsertRowRange, ListObject.TotalsRowRange, ListRow.Range, Name.RefersToRange, OLEObject.BottomRightCell, OLEObject.TopLeftCell, Pane.VisibleRange, Parameter.SourceRange, PivotCell.Range, PivotField.DataRange, PivotField.LabelRange, PivotItem.DataRange, PivotItem.LabelRange, PivotTable.DataBodyRange, PivotTable.ColumnRange, PivotTable.DataLabelRange, PivotTable.GetPivotData, PivotTable.PageRange, PivotTable.PageRangeCells, PivotTable.RowRange, PivotTable.TableRange1, PivotTable.TableRange2, QueryTable.Destination, QueryTable.ResultRange, Cells, Columns, Range, Rows, Item, Next, Previous, Resize, ColumnDifferences, CurrentArray, CurrentRegion, Dependents, DirectDependents, DirectPrecedents, End, EntireColumn, EntireRow, Find, FindNext, FindPrevious, MergeArea, Offset, Precedents, RowDifferences, SpecialCells, SpillingToRange, SpillParent, Ranges.Item, Scenario.ChangingCells, Shape.BottomRightCell, Shape.TopLeftCell, Sort.Rng, SortField.Key, Sparkline.Location, SparklineGroup.Location, SparklineGroups.Parent, TableObject.Destination, TableObject.ResultRange, Top10.AppliesTo, UniqueValues.AppliesTo, VPageBreak.Location, Window.ActiveCell, Window.VisibleRange, Window.RangeFromPoint, Window.RangeSelection, Worksheet.Cells, Worksheet.Columns, Worksheet.Range, Worksheet.Rows, Worksheet.CircularReference, Worksheet.UsedRange, Worksheet.XmlDataQuery, Worksheet.XmlMapQuery and WorkbookConnection.Ranges

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

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Name with its procedures ApplyNames, CreateNames and ListNames
Format with its procedures ApplyOutlineStyles, BorderAround, Borders, ClearFormats, ColumnWidth, DisplayFormat, Font, FormatConditions, Height, Left, NumberFormat, NumberFormatLocal, RowHeight, Style, Top, UseStandardHeight, UseStandardWidth and Width
Row with its procedures CalculateRowMajorOrder, EntireRow, ListHeaderRows, Row, RowDifferences and Rows
Cell with its procedures Cells, MergeCells, PivotCell, SetCellDataTypeFromCell and SpecialCells
Column with its procedures Column, ColumnDifferences, Columns, EntireColumn and TextToColumns
Copy with its procedures Copy, CopyFromRecordset and CopyPicture
Formula with its procedures Formula2, Formula2Local, Formula2R1C1, Formula2R1C1Local, FormulaArray, FormulaHidden, FormulaLocal, FormulaR1C1, FormulaR1C1Local and HasFormula
Show/Hide with its procedures Hidden, Show, ShowCard, ShowDependents, ShowDetail, ShowErrors and ShowPrecedents
Print with its procedures PageBreak, PrintOut and PrintPreview
Sort/Order with its procedures ReadingOrder, Sort and SortSpecial

Methods

These are the main methods of the Range class

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

ActiveCell.Activate

Delete - Deletes the object.

ActiveCell.Delete

Select - Selects the object.

ActiveCell.Select

Other Methods

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.

ActiveCell.AdvancedFilter Action:=xlFilterCopy

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

ActiveCell.AutoFill Destination:=

AutoFilter - Filters a list by using the AutoFilter.

ActiveCell.AutoFilter

CheckSpelling - Checks the spelling of an object.

ActiveCell.CheckSpelling

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

ActiveCell.Consolidate

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

Dim lngServiceID As Long: lngServiceID = 
Dim strLanguageCulture As String: strLanguageCulture = 
ActiveCell.ConvertToLinkedDataType ServiceID:=lngServiceID, LanguageCulture:=strLanguageCulture

DataSeries - Creates a data series in the specified range.

ActiveCell.DataSeries

ExportAsFixedFormat - Exports to a file of the specified format.

ActiveCell.ExportAsFixedFormat Type:=xlTypePDF

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)

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.

ActiveCell.Group

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

ActiveCell.Insert

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.

ActiveCell.NavigateArrow

NoteText - Returns or sets the cell note associated with the cell in the upper-left corner of the range. Read/write String. Cell notes have been replaced by range comments. For more information, see the Comment object.

Dim strNoteText As String
strNoteText = ActiveCell.NoteText()

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.

ActiveCell.Parse

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

ActiveCell.PasteSpecial

RemoveDuplicates - Removes duplicate values from a range of values.

ActiveCell.RemoveDuplicates

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.

Dim strWhat As String: strWhat = 
Dim booReplaced As Boolean
booReplaced = ActiveCell.Replace(What:=strWhat, Replacement:=)

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

Dim varRun As Variant
varRun = ActiveCell.Run()

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

ActiveCell.Speak

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

Dim lngGroupBy As Long: lngGroupBy = 
ActiveCell.Subtotal GroupBy:=lngGroupBy, Function:=xlAverage, TotalList:=

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

ActiveCell.Table

Properties

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

Dim lngCount As Long
lngCount = ActiveCell.Areas.Count

Areas.Item returns a single object from a collection.

Dim rngItem As Range
Set rngItem = ActiveCell.Areas(Index:=1)

Areas.Parent returns the parent object for the specified object. Read-only.

Dim objParent As Object
Set objParent = ActiveCell.Areas.Parent

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).

ActiveCell.AddIndent = True

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

Dim strAddress As String
strAddress = ActiveCell.Address

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

Dim strAddressLocal As String
strAddressLocal = ActiveCell.AddressLocal

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

Dim booAllowEdit As Boolean
booAllowEdit = ActiveCell.AllowEdit

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

Dim arsAreas As Areas
Set arsAreas = ActiveCell.Areas

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.

Dim chrsCharacters As Characters
Set chrsCharacters = ActiveCell.Characters

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

Dim cmmComment As Comment
Set cmmComment = ActiveCell.Comment

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

Dim ctdCommentThreaded As CommentThreaded
Set ctdCommentThreaded = ActiveCell.CommentThreaded

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

Dim lngCount As Long
lngCount = ActiveCell.Count

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

Dim lngCountLarge As Long
lngCountLarge = ActiveCell.CountLarge

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

Dim rngCurrentArray As Range
Set rngCurrentArray = ActiveCell.CurrentArray

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.

Dim rngCurrentRegion As Range
Set rngCurrentRegion = ActiveCell.CurrentRegion

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.

Dim rngDependents As Range
Set rngDependents = ActiveCell.Dependents

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.

Dim rngDirectDependents As Range
Set rngDirectDependents = ActiveCell.DirectDependents

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.

Dim rngDirectPrecedents As Range
Set rngDirectPrecedents = ActiveCell.DirectPrecedents

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.

Dim rngEnd As Range
Set rngEnd = ActiveCell.End(Direction:=xlDown)

Errors allows the user to access error checking options.

Dim errsErrors As Errors
Set errsErrors = ActiveCell.Errors

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

Dim booHasArray As Boolean
booHasArray = ActiveCell.HasArray

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.

Dim booHasRichDataType As Boolean
booHasRichDataType = ActiveCell.HasRichDataType

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.

Dim booHasSpill As Boolean
booHasSpill = ActiveCell.HasSpill

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

ActiveCell.HorizontalAlignment = xlHAlignCenter

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

Dim hypsHyperlinks As Hyperlinks
Set hypsHyperlinks = ActiveCell.Hyperlinks

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

ActiveCell.ID =

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

ActiveCell.IndentLevel = 15

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

Dim intInterior As Interior
Set intInterior = ActiveCell.Interior

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

ActiveCell(RowIndex:=1) =

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.

Dim xldLinkedDataTypeState As XlLinkedDataTypeState
xldLinkedDataTypeState = ActiveCell.LinkedDataTypeState

ListObject returns a ListObject object for the Range object.

Dim lstobjListObject As ListObject
Set lstobjListObject = ActiveCell.ListObject

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.

Dim xliLocationInTable As XlLocationInTable
xliLocationInTable = ActiveCell.LocationInTable

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

ActiveCell.Locked = True

MDX returns the MDX name for the specified Range object.

Dim strMDX As String
strMDX = ActiveCell.MDX

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.

Dim rngMergeArea As Range
Set rngMergeArea = ActiveCell.MergeArea

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

ActiveCell.Name =

Next returns a Range object that represents the next cell.

Dim rngNext As Range
Set rngNext = ActiveCell.Next

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

Dim rngOffset As Range
Set rngOffset = ActiveCell.Offset

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

ActiveCell.Orientation = xlDownward

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

ActiveCell.OutlineLevel =

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

Dim wsParent As Worksheet
Set wsParent = ActiveCell.Parent

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

Dim phnPhonetic As Phonetic
Set phnPhonetic = ActiveCell.Phonetic

Phonetics returns the Phonetics collection of the range.

Dim phnsPhonetics As Phonetics
Set phnsPhonetics = ActiveCell.Phonetics

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

Dim pvtfPivotField As PivotField
Set pvtfPivotField = ActiveCell.PivotField

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

Dim pvtiPivotItem As PivotItem
Set pvtiPivotItem = ActiveCell.PivotItem

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

Dim pvtPivotTable As PivotTable
Set pvtPivotTable = ActiveCell.PivotTable

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.

Dim rngPrecedents As Range
Set rngPrecedents = ActiveCell.Precedents

PrefixCharacter returns the prefix character for the cell.

Dim strPrefixCharacter As String
strPrefixCharacter = ActiveCell.PrefixCharacter

Previous returns a Range object that represents the previous cell.

Dim rngPrevious As Range
Set rngPrevious = ActiveCell.Previous

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

Dim qrytblQueryTable As QueryTable
Set qrytblQueryTable = ActiveCell.QueryTable

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

Dim lngCell1 As Long: lngCell1 = "A1"
Dim rngRange As Range
Set rngRange = ActiveCell.Range(Cell1:=lngCell1)

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

Dim rngResize As Range
Set rngResize = ActiveCell.Resize

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.

Dim booSavedAsArray As Boolean
booSavedAsArray = ActiveCell.SavedAsArray

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

Dim actsServerActions As Actions
Set actsServerActions = ActiveCell.ServerActions

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

ActiveCell.ShrinkToFit = True

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

Dim sneSoundNote As SoundNote
Set sneSoundNote = ActiveCell.SoundNote

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

Dim sgsSparklineGroups As SparklineGroups
Set sgsSparklineGroups = ActiveCell.SparklineGroups

SpillingToRange

Dim rngSpillingToRange As Range
Set rngSpillingToRange = ActiveCell.SpillingToRange

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

Dim rngSpillParent As Range
Set rngSpillParent = ActiveCell.SpillParent

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

Dim booSummary As Boolean
booSummary = ActiveCell.Summary

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

Dim strText As String
strText = ActiveCell.Text

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

Dim vldValidation As Validation
Set vldValidation = ActiveCell.Validation

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

ActiveCell.Value =

Value2 returns or sets the cell value.

ActiveCell.Value2 =

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

ActiveCell.VerticalAlignment = xlVAlignBottom

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

Dim wsWorksheet As Worksheet
Set wsWorksheet = ActiveCell.Worksheet

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

ActiveCell.WrapText = True

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.

Dim xphXPath As XPath
Set xphXPath = ActiveCell.XPath

Ranges.Count returns the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveWorkbook.Connections(1).Ranges.Count

Ranges.Item returns a Range object that represents a range of items in a workbook.

Dim rngItem As Range
Set rngItem = ActiveWorkbook.Connections(1).Ranges(Index:=1)

Ranges.Parent returns the parent object for the specified object. Read-only.

Dim objParent As Object
Set objParent = ActiveWorkbook.Connections(1).Ranges.Parent