Range Methods

Name

Returns

Parameters

Description

Activate

Variant

Selects the range cells

AddComment

Comment

[Text]

Adds the text specified by the parameter to the cell specified in the range. Must be a single cell range

Advanced Filter

Variant

[Action] As XlFilter Action, [Criteria Range],

[CopyToRange], [Unique]

Copies or filters the data in the current range. The Action parameter specifies whether a copy or filter is to take place. CriteriaRange optionally specifies the range containing the criteria. CopyToRange specifies the range that the filtered data will be copied to (if Action is xlFilterCopy)

ApplyNames

Variant

[Names], [Ignore Relative Absolute], [UseRowColumn Names], [OmitColumn], [OmitRow], [Order] As XlApplyNames Order,

[AppendLast]

Applies defined names to the formulas in a range. For example, if a cell contained =$A$1*100 and $A$1 was given the name "TopLeft", you could apply the "TopLeft" name to the range, resulting in the formula changing to =TopLeft*100. Note that there is no UnApplyNames method

ApplyOutline Styles

Variant

Applies the outline styles to the range

AutoComplete

String

String As String

Returns and tries to AutoComplete the word specified in the String parameter. Returns the complete word, if found. Returns an empty string if no word or more than one word is found

AutoFill

Variant

[Destination] As Range, [Type]

Uses the current range as the source to figure out how to AutoFill the range specified by the Destination parameter. The Type parameter can also be used to specify the type of fill to use (for example, xlFillCopy, xlFillDays)

Name

Returns

Parameters

Description

AutoFilter

Variant

[Field], [Criteria1], [Operator, [Criteria2], [Visible Drop Down]

Creates an auto-filter on the data in the range. See the AutoFilter object for details on the parameters

AutoFit

Variant

Changes the column widths in the range to best fit the data in the cells. The range must contain full rows or columns

AutoOutline

Variant

Creates an outline for the range

BorderAround

Variant

[LineStyle], [Weight] As XlBorder Weight, [ColorIndex], [Color]

Creates a border around the range with the associated line style (LineStyle), thickness (Weight), and color (ColorIndex)

Calculate

Variant

Calculates all the formulas in the range

CheckSpelling

Variant

[Custom Dictionary], [Ignore Uppercase], [Always Suggest], [SpellLang]

Checks the spelling of the text in the range. A custom dictionary can be specified (CustomDictionary), all uppercase words can be ignored (IgnoreUppercase), and Excel can be set to display a list of suggestions (AlwaysSuggest)

Clear

Variant

Clears the text in the cells of the range

ClearComments

Clears all the comments in the range cells

ClearContents

Variant

Clears the formulas and values in a range

ClearFormats

Variant

Clears the formatting in a range

ClearNotes

Variant

Clears comments from the cells in the range

ClearOutline

Variant

Clears the outline used in the current range

Column Differences

Range

[Comparison]

Returns the range of cells that are specific to the cell specified by the Comparison parameter

Name

Returns

Parameters

Description

Consolidate

[Function],

[Left Column],

[Create

Links]

Consolidates the source array of range reference strings in the Sources parameter and returns the results to the current range. The Function parameter can be used to set the consolidation function. Use the XLConsolidation Function constants

Copy

Variant

[Destination]

Copies the current range to the range specified by the parameter, or to the clipboard if no destination is specified

CopyFrom Recordset

Long

[Data] As Recordset, [MaxRows], [MaxColumns]

Copies the records from the ADO or DAO recordset specified by the Data parameter into the current range. The recordset can't contain OLE objects

CopyPicture Variant [Appearance], Copies the range into the clipboard

[Format] as a picture. The Appearance parameter can be used to specify whether the picture is copied as it looks on the screen or when printed. The Format parameter can specify the type of picture that will be put into the clipboard

Creates a named range for the items in the current range. Set Top to True to make the first row hold the names for the ranges below. Set Bottom to True to use the bottom row as the names. Set Left or Right to True to make the left or right column contain the Names, respectively

Cut Variant [Destination] Cuts the current range to the range specified by the parameter, or to the clipboard if no destination is specified

Creates a data series at the current range location

CreateNames Variant [Top], [Left],

DataSeries Variant [Rowcol], [Type]

Name

Returns

Parameters

Description

Delete

Variant

[Shift]

Deletes the cells in the current range and optionally shifts the cells in the direction specified by the Shift parameter. Use the XlDeleteShift Direction constants for the Shift parameter

DialogBox

Variant

Displays a dialog box defined by an Excel 4.0 macro sheet

Dirty

Selects a range to be recalculated when the next recalculation occurs

Edition Options

Variant

[Type] As XlEdition Type, [Option] As Xl Edition OptionsOption, [Name], [Reference], Appearance, [ChartSize], [Format]

Used on the Macintosh. EditionOptions set how the range should act when being used as the source (publisher) or target (subscriber) of the link. Editions are basically the same as Windows' DDE links

ExportAs FixedFormat

XlFixedFormatType,

[FileName],

[Quality],

[IncludeDoc

Properties],

[IgnorePrintAreas]

[OpenAfterPublish]

Exports a file to a format specified by using the xlFixedFormatType constants

FillDown

Variant

Copies the contents and formatting from the top row into the rest of the rows in the range

FillLeft

Variant

Copies the contents and formatting from the rightmost column into the rest of the columns in the range

FillRight

Variant

Copies the contents and formatting from the leftmost column into the rest of the columns in the range

FillUp

Variant

Copies the contents and formatting from the bottom row into the rest of the rows in the range

Name

Returns

Parameters

Description

Find

Range

[What] As Variant, [After], [LookIn], [LookAt], [SearchOrder], [Search Direction] As XlSearch Direction, [MatchCase], [MatchByte], [Search Format]

Looks through the current range for the text of data type specified by the What parameter. Use a single cell range in the After parameter to choose the starting position of the search. Use the LookIn parameter to decide where the search is going to take place

FindNext

Range

[After]

Finds the next instance of the search criteria defined with the Find method

FindPrevious

Range

[After]

Finds the previous instance of the search criteria defined with the Find method

Function Wizard

Variant

Displays the Function Wizard for the upper-left cell of the current range

GoalSeek

[ChangingCell] As Range

Returns True if the value specified by the Goal parameter is returned when changing the ChangingCell cell range

Group

Variant

[Start], [End], [By], [Periods]

Either demotes the outline in the range or groups the discontinuous ranges in the current Range object

Insert

Variant

[Shift], [CopyOrigin]

Inserts the equivalent rows or columns in the range into the range's worksheet

InsertIndent

[InsertAmount] As Long

Indents the range by the amount specified by the InsertAmount parameter

Justify

Variant

Evenly distributes the text in the cells from the current range

ListNames

Variant

Pastes the names of all the named ranges in the current range starting at the top-left cell in the range

Name

Returns

Parameters

Description

Merge

[Across]

Merges the cells in the range. Set the Across parameter to True to merge each row as a separate cell

NavigateArrow

Variant

[Toward Precedent], [ArrowNumber], [LinkNumber]

Moves through the tracer arrows in a workbook from the current range, returning the range of cells that make up the tracer arrow destination. Tracer arrows must be turned on. Use the ShowDependents and ShowPrecendents methods

NoteText

[Length]

Set/Get the cell notes associated with the cell in the current range

Parse

Variant

[ParseLine], [Destination]

Parses the string specified by the ParseLine parameter and returns it to the current range parsed out by column. Optionally, can specify the destination range with the Destination parameter. The ParseLine string should be in the " [ColumnA] [ColumnB]" format

PasteSpecial

Variant

[Paste], [Operation], [SkipBlanks], [Transpose]

Pastes the range from the clipboard into the current range. Use the Paste parameter to choose what to paste (for example, formulas, values). Use the Operation parameter to specify what to do with the paste. Set SkipBlanks to True to keep blank cells in the clipboard's range from being pasted. Set Transpose to True to transpose columns with rows

PrintOut

Variant

[From], [To], [Copies], [Preview], [Active Printer], [PrintToFile], [Collate], [PrToFile Name]

Prints the charts in the collection. The printer, number of copies, collation, and whether a print preview is desired can be specified with the parameters. Also, the sheets can be printed to a file using the PrintToFile and PrToFileName parameters. The From and To parameters can be used to specify the range of printed pages

Name

Returns

Parameters

Description

PrintPreview

Variant

[Enable Changes]

Displays the current range in a print preview. Set the EnableChanges parameter to False to disable the Margins and Setup buttons, hence not allowing the viewer to modify the page setup

Remove Duplicates

[Columns], [Header]

Removes duplicate values from a range of values

Remove Subtotal

Variant

Removes subtotals from the list in the current range

Replace

Boolean

[What] As Variant, [Replacement] As Variant, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [Search Format], [Replace Format]

Finds the text specified by the What parameter in the range. Replaces the found text with the Replacement parameter. Use the SearchOrder parameters with the XLSearchOrder constants to choose whether the search occurs by rows or by columns

Differences

Range

[Comparison]

Returns the range of cells that are specific to the cell specified by the Comparison parameter

Run

[Arg3 0]

Runs the Excel 4.0 macro specified by the current range. The potential arguments to the macro can be specified with the Argx parameters

Select

Variant

Selects the cells in the range

SetPhonetic

Creates a Phonetic object for each cell in the range

Show

Variant

Scrolls the Excel window to display the current range. This only works if the range is a single cell

Name

Returns

Parameters

Description

Show

Variant

[Remove]

Displays the dependents for the

Dependents

current single cell range using

tracer arrows

ShowErrors

Variant

Displays the source of the errors for

the current range using tracer

arrows

ShowPrecedent;

Variant

[Remove]

Displays the precedents for the cur-

rent single cell range using tracer

arrows

Sort

Variant

[Keyl],

Sorts the cells in the range. If the

[Orderl As

range contains only one cell, then

XlSortOrder],

the active region is searched. Use

[Key2], [Type],

the Keyl, Key2, and Key3

[Order2 As

parameters to set which columns

XlSortOrder],

will be the sort columns. Use the

[Key3], [Order3

Orderl, Order2, and Order3

As XlSortOrder],

parameters to set the sort order.

[Header As

Use the Header parameter to set

XlYesNoGuess],

whether the first row contains

[OrderCustom],

headers. Set the MatchCase

[MatchCase],

parameter to True to sort data and

[Orientation

to treat uppercase and lowercase

As XlSort

characters differently. Use the

Orientation],

Orientation parameter to choose

[SortMethodAs

whether rows are sorted or columns

XlSortMethod],

are sorted. Finally, the SortMethod

[DataOptionl

parameter is used to set the sort

As XlSortData

method for other languages (for

Option],

example, xlStroke or xlPinYin).

[DataOption2

Use the SortSpecial method for

As XlSortData

sorting in East Asian languages

Option],

[DataOption3

As XlSortData

Option]

Table continued on following page

Table continued on following page

Name

Returns

Parameters

Description

SortSpecial Variant

Speak

SpecialCells Range

SubscribeTo Variant

[SortMethod As XlSortMethod], [Key1], [Order1 As XlSortOrder], [Type], [Key2], [Order2 As XlSortOrder], [Key3], [Order3 As XlSortOrder], [Header As XlYesNoGuess], [OrderCustom], [MatchCase], [Orientation As XlSort Orientation], [DataOption1 As XlSortData Option], [DataOption2 As XlSortData Option], [DataOption3 As XlSortData Option]

[Speak Direction], [Speak Formulas]

Type As

XlCellType,

[Value]

[Edition] As

String,

[Format]

Sorts the data in the range using East Asian sorting methods. The parameters are the same as the Sort method

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

Returns the cells in the current range that contain some special attribute as defined by the Type parameter. For example, if Type is xlCellTypeBlanks, then a Range object containing all of the empty cells is returned

Only valid on the Macintosh. Defines the source of a link that the current range will contain

Name Returns Parameters Description

Creates a subtotal for the range. If the range is a single cell, then a subtotal is created for the current region. The GroupBy parameter specifies the field to group (for subtotaling). The Function parameter describes how the fields will be grouped. The TotalList parameter uses an array of field offsets that describe the fields that will be subtotaled. Set the Replace parameter to True to replace existing subtotals. Set PageBreaks to True for page breaks to be added after each group. Use the Summary-BelowData parameter to choose where the summary row will be added

Table

[Column

Input]

Creates a new data table at the current range

TextTo

Variant

[Destination],

Parses text in cells into several

Columns

[DataType As

columns. The Destination

XlTextParsing

specifies the range that the parsed

Type],

text will go into. The DataType

[Text

parameter can be used to choose

Qualifier As

whether the text is delimited or

XlText

fixed width. The TextQualifier

Qualifier],

parameter can specify which

[Consecutive

character denotes string data when

Delimiter],

parsing. Set the Consecutive

[Tab],

Delimiter to True for Excel to

[Semicolon],

treat consecutive delimiters as one.

[Comma],

Set the Tab, Semicolon, Comma, or

[Space],

Space parameter to True to use the

[Other],

associated character as the

[OtherChar],

delimiter. Set the Other parameter

[FieldInfo],

to True and specify an OtherChar

[Decimal

to use another character as the

Separator],

delimiter. FieldInfo takes a

[Thousands

two-dimensional array containing

Separator],

more parsing information. The

[Trailing

DecimalSeparator and

MinusNumbers]

ThousandsSeparator can specify

how numbers are treated when

parsing

Subtotal Variant [GroupBy] As

Long, [Function] As Xl

Consolidation Function, [TotalList], [Replace], [PageBreaks], [SummaryBelow Data]

Name

Returns

Parameters

Description

Ungroup

Variant

Either promotes the outline in the range or ungroups the range in a PivotTable report

UnMerge

Splits up a merged cell into single cells

0 0

Post a comment