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 =
Dim rngCells As Range
Set rngCells = rng.Cells
The following procedures can be used to set variables of type Range: Areas, AboveAverage.AppliesTo, 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, 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, SparklineGroups.Parent, TableObject.Destination, TableObject.ResultRange, Top10.AppliesTo, UniqueValues.AppliesTo, 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
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
Print with its procedures PageBreak, PrintOut and PrintPreview
Sort/Order with its procedures ReadingOrder, Sort and SortSpecial
Display/Show with its procedures Show, ShowCard, ShowDependents, ShowDetail, ShowErrors and ShowPrecedents
Value with its procedures Value and Value2
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.
Worksheets("Sheet1").Activate
Range("A1:C3").Select
Range("B2").Activate
Delete - Deletes the object.
Select - Selects the object.
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.
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria")
AutoFill - Performs an autofill on the cells in the specified range.
Set sourceRange = Worksheets("Sheet1").Range("A1:A2")
Set fillRange = Worksheets("Sheet1").Range("A1:A20")
sourceRange.AutoFill Destination:=fillRange
AutoFilter - Filters a list by using the AutoFilter.
Worksheets("Sheet1").Range("A1").AutoFilter _
Field:=1, _
Criteria1:="Otis", _
VisibleDropDown:=False
CheckSpelling - Checks the spelling of an object.
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
ConvertToLinkedDataType - Attempts to convert all the cells in the range to a Linked data type such as Stocks or Geography.
Range("E5").ConvertToLinkedDataType ServiceID:=268435456, LanguageCulture:= "en-US"
DataSeries - Creates a data series in the specified range.
Set dateRange = Worksheets("Sheet1").Range("A1:A12")
Worksheets("Sheet1").Range("A1").Formula = "31-JAN-1996"
dateRange.DataSeries Type:=xlChronological, Date:=xlMonth
ExportAsFixedFormat - Exports to a file of the specified format.
Find - Finds specific information in a range.
For Each c In [A1:C5] If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New Roman" End If Next`
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.
With Range("B2:E5")
.Insert xlShiftDown
.ClearFormats
End With
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.
Worksheets("Sheet1").Activate
Range("A1").Select
ActiveCell.NavigateArrow True, 1
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.
Worksheets("Sheet1").Range("A1").NoteText "This may change!"
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.
Worksheets("Sheet1").Columns("A").Parse _
parseLine:="[xxx] [xxxxxxxx]", _
destination:=Worksheets("Sheet1").Range("B1")
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
RemoveDuplicates - Removes duplicate values from a range of values.
ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes
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.
Worksheets("Sheet1").Columns("A").Replace _
What:="SIN", Replacement:="COS", _
SearchOrder:=xlByColumns, MatchCase:=True
Run - Runs the Microsoft Excel macro at this location. The range must be on a macro sheet.
Speak - Causes the cells of the range to be spoken in row order or column order.
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)
Table - Creates a data table based on input values and formulas that you define on a worksheet.
Set dataTableRange = Worksheets("Sheet1").Range("A1:K11")
Set rowInputCell = Worksheets("Sheet1").Range("A12")
Set columnInputCell = Worksheets("Sheet1").Range("A13")
Worksheets("Sheet1").Range("A1").Formula = "=A12*A13"
For i = 2 To 11
Worksheets("Sheet1").Cells(i, 1) = i - 1
Worksheets("Sheet1").Cells(1, i) = i - 1
Next i
dataTableRange.Table rowInputCell, columnInputCell
With Worksheets("Sheet1").Range("A1").CurrentRegion
.Rows(1).Font.Bold = True
.Columns(1).Font.Bold = True
.Columns.AutoFit
End With
Properties
Areas.Count returns a Long value that represents the number of objects in the collection.
Sub DisplayColumnCount()
Dim iAreaCount As Integer
Dim i As Integer
Worksheets("Sheet1").Activate
iAreaCount = Selection.Areas.Count
If iAreaCount <= 1 Then
MsgBox "The selection contains " & Selection.Columns.Count & " columns."
Else
For i = 1 To iAreaCount
MsgBox "Area " & i & " of the selection contains " & _
Selection.Areas(i).Columns.Count & " columns."
Next i
End If
End Sub
Areas.Item returns a single object from a collection.
If Selection.Areas.Count <> 1 Then
Selection.Areas.Item(1).Clear
End If
Areas.Parent returns the parent object for the specified object. Read-only.
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).
With Worksheets("Sheet1").Range("A1")
.HorizontalAlignment = xlHAlignDistributed
.AddIndent = True
End With
Address returns a String value that represents the range reference in the language of the macro.
Set mc = Worksheets("Sheet1").Cells(1, 1)
MsgBox mc.Address() ' $A$1
MsgBox mc.Address(RowAbsolute:=False) ' $A1
MsgBox mc.Address(ReferenceStyle:=xlR1C1) ' R1C1
MsgBox mc.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Worksheets(1).Cells(3, 3)) ' R[-2]C[-2]
AddressLocal returns the range reference for the specified range in the language of the user.
Set mc = Worksheets(1).Cells(1, 1)
MsgBox mc.AddressLocal() ' $A$1
MsgBox mc.AddressLocal(RowAbsolute:=False) ' $A1
MsgBox mc.AddressLocal(ReferenceStyle:=xlR1C1) ' Z1S1
MsgBox mc.AddressLocal(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Worksheets(1).Cells(3, 3)) ' Z(-2)S(-2)
AllowEdit returns a Boolean value that indicates if the range can be edited on a protected worksheet.
Sub UseAllowEdit()
Dim wksOne As Worksheet
Set wksOne = Application.ActiveSheet
' Protect the worksheet
wksOne.Protect
' Notify the user about editing cell A1.
If wksOne.Range("A1").AllowEdit = True Then
MsgBox "Cell A1 can be edited."
Else
Msgbox "Cell A1 cannot be edited."
End If
End Sub
Areas returns an Areas collection that represents all the ranges in a multiple-area selection.
If Selection.Areas.Count > 1 Then
MsgBox "Cannot do this to a multi-area selection."
End If
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.
With Worksheets("Sheet1").Range("A1")
.Value = "abcdefg"
.Characters(3, 1).Font.Bold = True
End With
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.
Sub DisplayColumnCount()
Dim iAreaCount As Integer
Dim i As Integer
Worksheets("Sheet1").Activate
iAreaCount = Selection.Areas.Count
If iAreaCount <= 1 Then
MsgBox "The selection contains " & Selection.Columns.Count & " columns."
Else
For i = 1 To iAreaCount
MsgBox "Area " & i & " of the selection contains " & _
Selection.Areas(i).Columns.Count & " columns."
Next i
End If
End Sub
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.
ActiveCell.CurrentArray.Select
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.
Worksheets("Sheet1").Activate
ActiveCell.CurrentRegion.Select
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.
Worksheets("Sheet1").Activate
Range("A1").Dependents.Select
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.
Worksheets("Sheet1").Activate
Range("A1").DirectDependents.Select
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.
Worksheets("Sheet1").Activate
Range("A1").DirectPrecedents.Select
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.
Range("B4").End(xlUp).Select
Errors allows the user to access error checking options.
Sub CheckForErrors()
Range("A1").Formula = "'12"
If Range("A1").Errors.Item(xlNumberAsText).Value = True Then
MsgBox "The number is written as text."
Else
MsgBox "The number is not written as text."
End If
End Sub
HasArray true if the specified cell is part of an array formula. Read-only Variant.
Worksheets("Sheet1").Activate
If ActiveCell.HasArray =True Then
MsgBox "The active cell is part of an array"
End If
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.
Worksheets("Sheet1").Activate
Set rr = Application.InputBox( _
prompt:="Select a range on this worksheet", _
Type:=8)
If rr.HasRichDataType = True Then
MsgBox "Every cell in the selection contains a Rich Data"
End If
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.
Worksheets("Sheet1").Activate
Set rr = Application.InputBox( _
prompt:="Select a range on this worksheet", _
Type:=8)
If rr.HasSpill = True Then
MsgBox "Every cell in the selection is part of a spilled range"
End If
Hidden returns or sets a Variant value that indicates if the rows or columns are hidden.
Worksheets("Sheet1").Columns("C").Hidden = True
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.
ActiveSheet.Range("A1").ID = "target"
IndentLevel returns or sets a value that represents the indent level for the cell or range. Can be an integer from 0 to 15.
With Range("A10")
.IndentLevel = 15
End With
Interior returns an Interior object that represents the interior of the specified object.
Sub SetColor()
Worksheets("Sheet1").Range("A1").Interior.ColorIndex = 8 ' Cyan
End Sub
Item returns a Range object that represents a range at an offset to the specified range.
Public Sub PrintAdresses()
Dim exampleRange As Excel.Range
With ThisWorkbook.Worksheets("ExampleSheet")
Set exampleRange = Application.Union(.Range("B2:D4"), .Range("A1"), .Range("Z1:AA20"))
End With
Debug.Print exampleRange.Item(1,1).Address 'Prints "$B$2"
Debug.Print exampleRange.Item(2,4).Address 'Prints "$E$3"
Debug.Print exampleRange.Item(20,40).Address 'Prints "$AO$21"
Debug.Print exampleRange.Item(2,"D").Address 'Prints "$E$3"
Debug.Print exampleRange.Item(20,"AN").Address 'Prints "$E$3"
End Sub
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.
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.
Worksheets("Sheet1").Activate
Select Case ActiveCell.LocationInTable
Case Is = xlRowHeader
MsgBox "Active cell is part of a row header"
Case Is = xlColumnHeader
MsgBox "Active cell is part of a column header"
Case Is = xlPageHeader
MsgBox "Active cell is part of a page header"
Case Is = xlDataHeader
MsgBox "Active cell is part of a data header"
Case Is = xlRowItem
MsgBox "Active cell is part of a row item"
Case Is = xlColumnItem
MsgBox "Active cell is part of a column item"
Case Is = xlPageItem
MsgBox "Active cell is part of a page item"
Case Is = xlDataItem
MsgBox "Active cell is part of a data item"
Case Is = xlTableBody
MsgBox "Active cell is part of the table body"
End Select
Locked returns or sets a Variant value that indicates if the boolean is locked.
Worksheets("Sheet1").Range("A1:G37").Locked = False
Worksheets("Sheet1").Protect
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.
Set ma = Range("a3").MergeArea
If ma.Address = "$A$3" Then
MsgBox "not merged"
Else
ma.Cells(1, 1).Value = "42"
End If
Name returns or sets a Variant value that represents the name of the string.
Next returns a Range object that represents the next cell.
Offset returns a Range object that represents a range that's offset from the specified range.
Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
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.
Worksheets("Sheet1").Rows(2).OutlineLevel = 1
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.
Set objPhon = ActiveCell.Phonetics
With objPhon
For Each objPhonItem in objPhon
MsgBox "Phonetic object: " & .Text
Next
End With
PivotField returns a PivotField object that represents the PivotTable field containing the upper-left corner of the specified range.
Worksheets("Sheet1").Activate
MsgBox "The active cell is in the field " & _
ActiveCell.PivotField.Name
PivotItem returns a PivotItem object that represents the PivotTable item containing the upper-left corner of the specified range.
Worksheets("Sheet1").Activate
MsgBox "The active cell is in the item " & _
ActiveCell.PivotItem.Name
PivotTable returns a PivotTable object that represents the PivotTable report containing the upper-left corner of the specified range.
Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
pvtTable.PivotFields("Country").CurrentPage = "Canada"
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.
Worksheets("Sheet1").Activate
Range("A1").Precedents.Select
PrefixCharacter returns the prefix character for the cell.
MsgBox "The prefix character is " & _
Worksheets("Sheet1").Range("A1").PrefixCharacter
Previous returns a Range object that represents the previous cell.
Worksheets("Sheet1").Activate
ActiveCell.Previous.Select
QueryTable returns a QueryTable object that represents the query table that intersects the specified Range object.
Worksheets(1).Range("a10").QueryTable.Refresh
Range returns a Range object that represents a cell or a range of cells.
With Worksheets("Sheet1").Range("B2:C4")
.Range("A1").Value = 3.14159
End With
Resize resizes the specified range. Returns a Range object that represents the resized range.
Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select
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.
Worksheets("Sheet1").Activate
Set rr = Application.InputBox( _
prompt:="Select a range on this worksheet", _
Type:=8)
If rr.SavedAsArray = True Then
MsgBox "Every cell in the selection is part of a spilled range"
End If
ServerActions specifies the actions that can be performed on the SharePoint server for a Range object.
ShrinkToFit returns or sets a value that indicates if text automatically shrinks to fit in the available column width.
Rows(1).ShrinkToFit = True
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.
Set rr = Application.InputBox( _
prompt:="Select a cell on this worksheet", _
Type:=8)
If rr.HasSpill = True Then
MsgBox "The spill is coming from " & rr.SpillParent.Address
Else
MsgBox "This cell is not part of a spill range"
End If
Summary true if the range is an outlining summary row or column. The range should be a row or a column.
With Worksheets("Sheet1").Rows(4)
If .Summary = True Then
.Font.Bold = True
.Font.Italic = True
End If
End With
Text returns the formatted text for the specified string. Read-only String.
Option Explicit
Public Sub DifferencesBetweenValueAndTextProperties()
Dim cell As Range
Set cell = Worksheets("Sheet1").Range("A1")
cell.Value = 1198.3
cell.NumberFormat = "$#,##0_);($#,##0)"
MsgBox "'" & cell.Value & "' is the value." 'Returns: "'1198.3' is the value."
MsgBox "'" & cell.Text & "' is the text." 'Returns: "'$1,198' is the text."
End Sub
Validation returns the Validation object that represents data validation for the specified range.
Range("e5").Validation.IgnoreBlank = True
VerticalAlignment returns or sets a value that represents the vertical alignment of the specified object.
Worksheet returns a Worksheet object that represents the worksheet containing the specified range.
MsgBox ActiveCell.Worksheet.Name
WrapText returns or sets a Variant value that indicates if Microsoft Excel wraps the text in the string.
Worksheets("Sheet1").Range("B2").Value = _
"This text should wrap in a cell."
Worksheets("Sheet1").Range("B2").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.
Ranges.Count returns the number of objects in the collection.
Ranges.Item returns a Range object that represents a range of items in a workbook.
Ranges.Parent returns the parent object for the specified object. Read-only.