How to use Excel class Range

Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-d range. Because ranges are the core of Excel VBA provides many procedures. For some often used methods separate pages have been made: AddComment, Clear and variations such as ClearComments and ClearContents

First the most prominent methods are presented: Activate, Insert, Copy, Delete and Select.

After that the othere methods of the Excel class Range are explained: AdvancedFilter, AllocateChanges, ApplyNames, ApplyOutlineStyles, AutoComplete, AutoFill, AutoFilter, AutoFit, AutoOutline, BorderAround, Calculate, CalculateRowMajorOrder, CheckSpelling, ColumnDifferences, Consolidate, CopyFromRecordset, CopyPicture, CreateNames, Cut, DataSeries, DialogBox, Dirty, DiscardChanges, ExportAsFixedFormat, FillDown, FillLeft, FillRight, FillUp, Find, FindNext, FindPrevious, FlashFill, FunctionWizard, Group, InsertIndent, Justify, ListNames, Merge, NavigateArrow, Parse, PasteSpecial, PrintOut, PrintPreview, RemoveDuplicates, RemoveSubtotal, Replace, RowDifferences, Run, SetPhonetic, Show, ShowDependents, ShowErrors, ShowPrecedents, Sort, SortSpecial, Speak, Subtotal, Table, TextToColumns, Ungroup, UnMerge.

menu

Set Range

You can Set a Range variable by calling a method that returns a variable of that type, for example:

Dim wb As Workbook: Set wb =
Dim ws As Worksheet: Set ws = wb.Sheets("Data")
Set rng = ws.Range(Cell1:="Company")

Because there are so many ways to Set a range variable, they are discussed on a separate page...related

For Each in collection Range

The code below shows how you can loop through a the Cells collection - each iteration returning a single cell range object. Follow this link for an extensive discussion on how to loop through ranges ... related

Dim rng As Range: Set rng = Application.Range("Data!B2:C3")
Dim cel As Range
For Each cel In rng.Cells
    With cel
        Debug.Print .Address & ":" & .Value
    End With
Next cel

RangeNameExists

Tests if the range name exists

Public Function RangeNameExists(strName As String, Optional wb As Workbook) As Boolean
'Returns TRUE if the range name exists
Dim n As Name
    If wb Is Nothing Then
        Set wb = ActiveWorkbook
    End If
    RangeNameExists = False
    For Each n In wb.Names
        If UCase(n.Name) = UCase(strName) Then
            RangeNameExists = True
            Exit Function
        End If
    Next n
End Function

IsInRange

Tests if rng1 is a subset of rng2.

Public Function IsInRange(rng1, rng2) As Boolean
'Returns True if rng1 is a subset of rng2
'?IsInRange(Range("A1"), Range("A1:A2"))=True
'?IsInRange(Range("A1:A2"), Range("A1"))=False
    IsInRange = False
    If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
        If rng1.Parent.Name = rng2.Parent.Name Then
            If Union(rng1, rng2).Address = rng2.Address Then
                IsInRange = True
            End If
        End If
    End If
End Function

AddComment Range

Adds a comment to the range.

Dim cmm As Comment Dim rng As Range Set cmm = rng.AddComment()

Arguments

Text The comment text.

Copy Range

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

Dim rng As Range 
rng.Copy 

Arguments

Destination Specifies the new range to which the specified range will be copied. If this argument is omitted Microsoft Excel copies the range to the Clipboard.

Delete Range

Delete the Range

Dim rng As Range 
rng.Delete 

Arguments

Shift Used only with Range objects. Specifies how to shift cells to replace deleted cells. Can be one of the following XlDeleteShiftDirection constants: xlShiftToLeftor xlShiftUp. If this argument is omitted Microsoft Excel decides based on the shape of the range.

Activate Range

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

Dim rng As Range 
rng.Activate 

Select Range

Selects the object.

Dim rng As Range rng.Select 

AdvancedFilter Range

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.

Dim rng As Range rng.AdvancedFilter Action:= 

Arguments

Action One of the constants of XlFilterAction specifying whether to make a copy or filter the list in place.
CriteriaRange The criteria range. If this argument is omitted there are no criteria.
CopyToRange The destination range for the copied rows if Action isxlFilterCopy. Otherwise this argument is ignored.
Unique True to filter unique records only. False to filter all records that meet the criteria. The default value is False.

AllocateChanges Range

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

Dim rng As Range 
rng.AllocateChanges 

ApplyNames Range

Applies names to the cells in the specified range.

Dim rng As Range 
rng.ApplyNames 

Arguments

Names An array of the names to be applied. If this argument is omitted all names on the sheet are applied to the range.
IgnoreRelativeAbsolute True to replace references with names regardless of the reference types of either the names or references. False to replace absolute references only with absolute names relative references only with relative names and mixed references only with mixed names. The default value is True.
UseRowColumnNames True to use the names of row and column ranges that contain the specified range if names for the range cannot be found. False to ignore the OmitColumn and OmitRow arguments. The default value is True.
OmitColumn True to replace the entire reference with the row-oriented name. The column-oriented name can be omitted only if the referenced cell is in the same column as the formula and is within a row-oriented named range. The default value is True.
OmitRow True to replace the entire reference with the column-oriented name. The row-oriented name can be omitted only if the referenced cell is in the same row as the formula and is within a column-oriented named range. The default value is True.
Order Determines which range name is listed first when a cell reference is replaced by a row-oriented and column-oriented range name.
AppendLast True to replace the definitions of the names in Names and also replace the definitions of the last names that were defined. False to replace the definitions of the names in Names only. The default value is False.

ApplyOutlineStyles Range

Applies outlining styles to the specified range.

Dim rng As Range 
rng.ApplyOutlineStyles 

AutoComplete Range

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.

Dim str As String 
Dim rng As Range 
str = rng.AutoComplete(String:= )

Arguments

String The string to complete

AutoFill Range

Performs an autofill on the cells in the specified range.

Dim rng As Range 
rng.AutoFill Destination:= 

Arguments

Destination The cells to be filled. The destination must include the source range.
Type Specifies the fill type.

AutoFilter Range

Filters a list using the AutoFilter.

Dim rng As Range 
rng.AutoFilter 

Arguments

Field The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).
Criteria1 The criteria (a string; for example and#8220;101and#8221;). Use and#8220;and#61;and#8221; to find blank fields or useand#8220;andlt;andgt;and#8221; to find nonblank fields. If this argument is omitted the criteria is All. If Operator is xlTop10Items Criteria1 specifies the number of items (for example 10).
Operator One of the constants of XlAutoFilterOperator specifying the type of filter.
Criteria2 The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria.
VisibleDropDown True to display the AutoFilter drop-down arrow for the filtered field. False to hide the AutoFilter drop-down arrow for the filtered field. True by default.

AutoFit Range

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

Dim rng As Range 
rng.AutoFit 

AutoOutline Range

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.

Dim rng As Range 
rng.AutoOutline 

BorderAround Range

Adds a border to a range and sets the Color, LineStyle, and Weight properties for the new border.

Dim rng As Range 
rng.BorderAround 

Arguments

LineStyle One of the constants of XlLineStyle specifying the line style for the border.
Weight The border weight.
ColorIndex The border color as an index into the current color palette or as an XlColorIndex constant.
Color The border color as an RGB value.
ThemeColor The theme color as an index into the current color theme or as an XlThemeColor value.

Calculate Range

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

Dim rng As Range 
rng.Calculate 

CalculateRowMajorOrder Range

Calculates a specfied range of cells.

Dim rng As Range 
rng.CalculateRowMajorOrder 

CheckSpelling Range

Checks the spelling of an object.

Dim rng As Range 
rng.CheckSpelling 

Arguments

CustomDictionary A string that indicates the file name of the custom dictionary to be examined if the word isn't found in the main dictionary. If this argument is omitted the currently specified dictionary is used.
IgnoreUppercase True to have Microsoft Excel ignore words that are all uppercase. False to have Microsoft Excel check words that are all uppercase. If this argument is omitted the current setting will be used.
AlwaysSuggest True to have Microsoft Excel display a list of suggested alternate spellings when an incorrect spelling is found. False to have Microsoft Excel wait for you to input the correct spelling. If this argument is omitted the current setting will be used.
SpellLang The language of the dictionary being used. Can be one of the MsoLanguageID values.

ColumnDifferences Range

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

Dim rng As Range 
Dim rng As Range 
Set rng = rng.ColumnDifferences(Comparison:= )

Arguments

Comparison A single cell to compare to the specified range.

Consolidate Range

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

Dim var As Variant
Dim rng As Range 
var = rng.Consolidate()

Arguments

Sources The sources of the consolidation as an array of text reference strings in R1C1-style notation. The references must include the full path of sheets to be consolidated.
Function One of the constants of XlConsolidationFunction which specifies the type of consolidation.
TopRow True to consolidate data based on column titles in the top row of the consolidation ranges. False to consolidate data by position. The default value is False.
LeftColumn True to consolidate data based on row titles in the left column of the consolidation ranges. False to consolidate data by position. The default value is False.
CreateLinks True to have the consolidation use worksheet links. False to have the consolidation copy the data. The default value is False.

CopyFromRecordset Range

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 lng As Long 
Dim rng As Range 
lng = rng.CopyFromRecordset(Data:= )

Arguments

Data The Recordset object to copy into the range.
MaxRows The maximum number of records to copy onto the worksheet. If this argument is omitted all the records in the Recordset object are copied.
MaxColumns The maximum number of fields to copy onto the worksheet. If this argument is omitted all the fields in the Recordset object are copied.

CopyPicture Range

Copies the selected object to the Clipboard as a picture.

Dim rng As Range 
rng.CopyPicture 

Arguments

Appearance Specifies how the picture should be copied.
Format . The format of the picture.

CreateNames Range

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

Dim rng As Range 
rng.CreateNames 

Arguments

Top True to create names by using labels in the top row. The default value is False.
Left True to create names by using labels in the left column. The default value is False.
Bottom True to create names by using labels in the bottom row. The default value is False.
Right True to create names by using labels in the right column. The default value is False.

Cut Range

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

Dim rng As Range 
rng.Cut 

Arguments

Destination The range where the object should be pasted. If this argument is omitted the object is cut to the Clipboard.

DataSeries Range

Creates a data series in the specified range.

Dim rng As Range 
rng.DataSeries 

Arguments

Rowcol Can be the xlRows or xlColumns constant to have the data series entered in rows or columns respectively. If this argument is omitted the size and shape of the range is used.
Type The type for the data series.
Date If the Type argument is xlChronological the Date argument indicates the step date unit.
Step The step value for the series. The default value is 1.
Stop The stop value for the series. If this argument is omitted Microsoft Excel fills to the end of the range.
Trend True to create a linear trend or growth trend. False to create a standard data series. The default value is False.

DialogBox Range

Displays a dialog box defined by a dialog box definition table on a Microsoft Excel 4.0 macro sheet. Returns the number of the chosen control, or returns False if the user clicks the Cancel button.

Dim var As Variant 
Dim rng As Range 
var = rng.DialogBox()

Dirty Range

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

Dim rng As Range 
rng.Dirty 

DiscardChanges Range

Discards all changes in the edited cells of the range.

Dim rng As Range 
rng.DiscardChanges 

ExportAsFixedFormat Range

Exports to a file of the specified format.

Dim rng As Range 
rng.ExportAsFixedFormat Type:= 

Arguments

Type The type of file format to export to.
Filename The file name of the file to be saved. You can include a full path or Excel saves the file in the current folder.
Quality Optional XlFixedFormatQuality. Specifies the quality of the published file.
IncludeDocProperties True to include the document properties; otherwise False.
IgnorePrintAreas True to ignore any print areas set when publishing; otherwise False.
From The number of the page at which to start publishing. If this argument is omitted publishing starts at the beginning.
To The number of the last page to publish. If this argument is omitted publishing ends with the last page.
OpenAfterPublish True to display the file in the viewer after it is published; otherwise False.
FixedFormatExtClassPtr Pointer to the FixedFormatExt class.

FillDown Range

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.

Dim rng As Range 
rng.FillDown 

FillLeft Range

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.

Dim rng As Range 
rng.FillLeft 

FillRight Range

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.

Dim rng As Range 
rng.FillRight 

FillUp Range

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.

Dim rng As Range 
rng.FillUp 

Find Range

Finds specific information in a range.

Dim rng As Range 
Dim rngFound As Range 
Set rngFound = rng.Find(What:= )

Arguments

What The data to search for. Can be a string or any Microsoft Excel data type.
After The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you do no specify this argument the search starts after the cell in the upper-left corner of the range.
LookIn The type of information.
LookAt Can be one of the following XlLookAt constants: xlWhole or xlPart.
SearchOrder Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.
SearchDirection The search direction.
MatchCase True to make the search case sensitive. The default value is False.
MatchByte Used only if you have selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.
SearchFormat The search format.

FindNext Range

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

Dim rng As Range 
Dim rngFound As Range 
Set rngFound = rng.FindNext()

Arguments

After The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Be aware that Aftermust be a single cell in the range. Remember that the search begins after this cell; the specified cell is not searched until the method wraps back around to this cell. If this argument is not specified the search starts after the cell in the upper-left corner of the range.

FindPrevious Range

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

Dim rng As Range 
Dim rng As Range 
Set rng = rng.FindPrevious()

Arguments

After The cell before which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins before this cell; the specified cell isn't searched until the method wraps back around to this cell. If this argument isn't specified the search starts before the upper- left cell in the range.

FlashFill Range

TRUE indicates that the Excel Flash Fill feature has been enabled and active.

Dim rng As Range 
rng.FlashFill 

FunctionWizard Range

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

Dim rng As Range 
rng.FunctionWizard 

Group Range

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.

Dim rng As Range 
rng.Group 

Arguments

Start The first value to be grouped. If this argument is omitted or True the first value in the field is used.
End The last value to be grouped. If this argument is omitted or True the last value in the field is used.
By If the field is numeric this argument specifies the size of each group. If the field is a date this argument specifies the number of days in each group if element 4 in the Periods array is True and all the other elements are False. Otherwise this argument is ignored. If this argument is omitted Microsoft Excel automatically chooses a default group size.
Periods An array of Boolean values that specify the period for the group described in the Remarks section. If an element in the array is True a group is created for the corresponding time; if the element is False no group is created. If the field isn't a date field this argument is ignored.

Insert Range

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

Dim rng As Range 
rng.Insert 

Arguments

Shift Specifies which way to shift the cells. Can be one of the following XlInsertShiftDirection constants: xlShiftToRight or xlShiftDown. If this argument is omitted Microsoft Excel decides based on the shape of the range.
CopyOrigin The copy origin.

InsertIndent Range

Adds an indent to the specified range.

Dim rng As Range rng.InsertIndent InsertAmount:= 

Arguments

InsertAmount The amount to be added to the current indent.

Justify Range

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

Dim rng As Range 
rng.Justify 

ListNames Range

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

Dim rng As Range 
rng.ListNames 

Merge Range

Creates a merged cell from the specified Range object.

Dim rng As Range 
rng.Merge 

Arguments

Across True to merge cells in each row of the specified range as separate merged cells. The default value is False.

NavigateArrow Range

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.

Dim rng As Range 
rng.NavigateArrow 

Arguments

TowardPrecedent Specifies the direction to navigate: True to navigate toward precedents False to navigate toward dependent.
ArrowNumber Specifies the arrow number to navigate; corresponds to the numbered reference in the cell's formula.
LinkNumber If the arrow is an external reference arrow this argument indicates which external reference to follow. If this argument is omitted the first external reference is followed.

Parse Range

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.

Dim rng As Range 
rng.Parse 

Arguments

ParseLine A string that contains left and right brackets to indicate where the cells should be split.
Destination A Range object that represents the upper-left corner of the destination range for the parsed data. If this argument is omitted Microsoft Excel parses in place.

PasteSpecial Range

Pastes a Range from the Clipboard into the specified range.

Dim rng As Range 
rng.PasteSpecial 

Arguments

Paste The part of the range to be pasted.
Operation The paste operation.
SkipBlanks True to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.
Transpose True to transpose rows and columns when the range is pasted.The default value is False.

PrintOut Range

Prints the object.

Dim rng As Range 
rng.PrintOut 

Arguments

From The number of the page at which to start printing. If this argument is omitted printing starts at the beginning.
To The number of the last page to print. If this argument is omitted printing ends with the last page.
Copies The number of copies to print. If this argument is omitted one copy is printed.
Preview True to have Microsoft Excel invoke print preview before printing the object.False (or omitted) to print the object immediately.
ActivePrinter Sets the name of the active printer.
PrintToFile True to print to a file. If PrToFileName is not specified Microsoft Excel prompts the user to enter the name of the output file.
Collate True to collate multiple copies.
PrToFileName If PrintToFile is set to True this argument specifies the name of the file you want to print to.

PrintPreview Range

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

Dim rng As Range rng.PrintPreview 

Arguments

EnableChanges Pass a Boolean value to specify if the user can change the margins and other page setup options available in print preview.

RemoveDuplicates Range

Removes duplicate values from a range of values.

Dim rng As Range 
rng.RemoveDuplicates 

Arguments

Columns Array of indexes of the columns that contain the duplicate information.
Header Specifies whether the first row contains header information. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header.

RemoveSubtotal Range

Removes subtotals from a list.

Dim rng As Range 
rng.RemoveSubtotal 

Replace Range

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 boo As Boolean 
Dim rng As Range 
boo = rng.Replace(What:= ,Replacement:= )

Arguments

What The string you want Microsoft Excel to search for.
Replacement The replacement string.
LookAt Can be one of the following XlLookAt constants: xlWhole or xlPart.
SearchOrder Can be one of the following XlSearchOrder constants: xlByRows orxlByColumns.
MatchCase True to make the search case sensitive.
MatchByte You can use this argument only if you've selected or installed double-byte language support in Microsoft Excel. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.
SearchFormat The search format for the method.
ReplaceFormat The replace format for the method.

RowDifferences Range

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

Dim rng As Range 
Dim rng As Range 
Set rng = rng.RowDifferences(Comparison:= )

Arguments

Comparison A single cell to compare with the specified range.

Run Range

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

Dim rng As Range 
rng.Run 

Arguments

Arg1 The arguments that should be passed to the function.
Arg2 Etcetera

SetPhonetic Range

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

Dim rng As Range 
rng.SetPhonetic 

Show Range

Scrolls through the contents of the active window to move the range into view.

Dim rng As Range 
rng.Show 

ShowDependents Range

Draws tracer arrows to the direct dependents of the range.

Dim rng As Range rng.ShowDependents 

Arguments

Remove True to remove one level of tracer arrows to direct dependents. False to expand one level of tracer arrows. The default value is False.

ShowErrors Range

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.

Dim rng As Range 
rng.ShowErrors 

ShowPrecedents Range

Draws tracer arrows to the direct precedents of the range.

Dim rng As Range 
rng.ShowPrecedents 

Arguments

Remove True to remove one level of tracer arrows to direct precedents. False to expand one level of tracer arrows. The default value is False.

Sort Range

Sorts a range of values.

Dim rng As Range 
rng.Sort 

Arguments

Key1 Specifies the first sort field either as a range name (String) or Rangeobject; determines the values to be sorted.
Order1 Determines the sort order for the values specified in Key1.
Key2 Second sort field; cannot be used when sorting a pivot table.
Type Specified which elements are to be sorted.
Order2 Determines the sort order for the values specified in Key2.
Key3 Third sort field; cannot be used when sorting a pivot table.
Order3 Determines the sort order for the values specified in Key3.
Header Specifies whether the first row contains header information. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header.
OrderCustom Specifies a one-based integer offset into the list of custom sort orders.
MatchCase Set to True to perform a case-sensitive sort False to perform non-case sensitive sort; cannot be used with pivot tables.
Orientation Specifies if the sort should be in acending or decending order.
SortMethod Specifies the sort method.
DataOption1 Specifies how to sort text in the range specified in Key1; does not apply to pivot table sorting.
DataOption2 Specifies how to sort text in the range specified in Key2; does not apply to pivot table sorting.
DataOption3 Specifies how to sort text in the range specified in Key3; does not apply to pivot table sorting

SortSpecial Range

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.

Dim rng As Range 
rng.SortSpecial 

Arguments

SortMethod The type of sort. Some of these constants may not be available to you depending on the language support (U.S. English for example) that you've selected or installed.
Key1 The first sort field as either text (a PivotTable field or range name) or a Range object (Dept or Cells(1 1) for example).
Order1 The sort order for the field or range specified in the Key1 argument.
Type Specifies which elements are to be sorted. Use this argument only when sorting PivotTable reports.
Key2 The second sort field as either text (a PivotTable field or range name) or a Range object. If you omit this argument there's no second sort field. Cannot be used when sorting PivotTable reports.
Order2 The sort order for the field or range specified in the Key2 argument. Cannot be used when sorting PivotTable reports.
Key3 The third sort field as either text (a range name) or a Range object. If you omit this argument there's no third sort field. Cannot be used when sorting PivotTable reports.
Order3 The sort order for the field or range specified in the Key3 argument. Cannot be used when sorting PivotTable reports.
Header Specifies whether or not the first row contains headers. Cannot be used when sorting PivotTable reports.
OrderCustom This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom (normal sort order) is used.
MatchCase True to do a case-sensitive sort; False to do a sort that's not case sensitive. Cannot be used when sorting PivotTable reports.
Orientation The sort orientation.
DataOption1 Specifies how to sort text in Key1. Cannot be used when sorting PivotTable reports.
DataOption2 Specifies how to sort text in Key2. Cannot be used when sorting PivotTable reports.
DataOption3 Specifies how to sort text in Key3. Cannot be used when sorting PivotTable reports.

Speak Range

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

Dim rng As Range 
rng.Speak 

Arguments

SpeakDirection The speak direction by rows or columns.
SpeakFormulas True will cause formulas to be sent to the Text-To-Speech (TTS) engine for cells that have formulas. The value is sent if the cells do not have formulas.False (default) will cause values to always be sent to the TTS engine.

Subtotal Range

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

Dim rng As Range 
rng.Subtotal GroupBy:= ,Function:= ,TotalList:= 

Arguments

GroupBy The field to group by as a one-based integer offset. For more information see the example.
Function . The subtotal function.
TotalList An array of 1-based field offsets indicating the fields to which the subtotals are added. For more information see the example.
Replace True to replace existing subtotals. The default value isTrue.
PageBreaks True to add page breaks after each group. The default value is False.
SummaryBelowData . Places the summary data relative to the subtotal.

Table Range

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

Dim rng As Range 
rng.Table 

Arguments

RowInput A single cell to use as the row input for your table.
ColumnInput A single cell to use as the column input for your table.

TextToColumns Range

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

Dim rng As Range 
rng.TextToColumns 

Arguments

Destination A Range object that specifies where Microsoft Excel will place the results. If the range is larger than a single cell the top left cell is used.
DataType The format of the text to be split into columns.
TextQualifier Specifies whether to use single double or no quotes as the text qualifier.
ConsecutiveDelimiter True to have Microsoft Excel consider consecutive delimiters as one delimiter. The default value is False.
Tab True to have DataType be xlDelimited and to have the tab character be a delimiter. The default value is False.
Semicolon True to have DataType be xlDelimited and to have the semicolon be a delimiter. The default value is False.
Comma True to have DataType be xlDelimited and to have the comma be a delimiter. The default value is False.
Space True to have DataType be xlDelimited and to have the space character be a delimiter. The default value is False.
Other True to have DataType be xlDelimited and to have the character specified by the OtherChar argument be a delimiter. The default value is False.
OtherChar (required if Other is True). The delimiter character whenOther is True. If more than one character is specified only the first character of the string is used; the remaining characters are ignored.
FieldInfo An array containing parse information for the individual columns of data. The interpretation depends on the value of DataType. When the data is delimited this argument is an array of two-element arrays with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based) and the second element is one of the xlColumnDataTypeconstants specifying how the column is parsed.
DecimalSeparator The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.
ThousandsSeparator The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.
TrailingMinusNumbers Numbers that begin with a minus character.

Ungroup Range

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.

Dim rng As Range rng.Ungroup 

UnMerge Range

Separates a merged area into individual cells.

Dim rng As Range rng.UnMerge 

For more info see

Microsoft Office Object reference on Range