Range (object)

<< Click to Display Table of Contents >>

Range (object)

Access paths (for arbitrary cell ranges):

Application à Workbooks à Item à Sheets à Item à Range

Application à Workbooks à ActiveSheet à Range

Application à ActiveWorkbook à ActiveSheet à Range

Application à ActiveSheet à Range

Application à Range

Access paths (for entire table rows):

Application à Workbooks à Item à Sheets à Item à Rows à Item

Application à Workbooks à ActiveSheet à Rows à Item

Application à ActiveWorkbook à ActiveSheet à Rows à Item

Application à ActiveSheet à Rows à Item

Application à Rows à Item

Access paths (for entire table columns):

Application à Workbooks à Item à Sheets à Item à Columns à Item

Application à Workbooks à ActiveSheet à Columns à Item

Application à ActiveWorkbook à ActiveSheet à Columns à Item

Application à ActiveSheet à Columns à Item

Application à Columns à Item

Access paths (for the currently selected cells):

Application à Workbooks à Item à Sheets à Item à Selection

Application à Workbooks à ActiveSheet à Selection

Application à ActiveWorkbook à ActiveSheet à Selection

Application à ActiveSheet à Selection

Application à Selection

 1  Description

Range represents a specific cell range in a worksheet (Sheet). This range can contain an arbitrary number of cells, from one cell to the whole worksheet.

You can use a Range object to get and set among other things the contents and formatting of the cells in the represented range, to copy the range to the clipboard, etc.

 2  Access to the object

There are many ways to access a Range object:

1.You can access the Range object directly by indicating the start and end cell. Example:

' Add a comment to the cell C10

pm.ActiveSheet.Range("C10").Comment = "A comment"

2.The Sheet.Selection property returns a Range object that represents the active selection, i.e. the currently selected cells. Example:

' Format the current selection with the font "Courier New"

pm.ActiveSheet.Selection.Font.Name = "Courier New"

3.The Rows collection returns Range objects that represent an entire row of the worksheet. You can access the Rows collection through the Sheet.Rows object. Example:

' Hide row 2 of the worksheet

pm.ActiveSheet.Rows(2).Hidden = True

4.The Columns collection returns Range objects that represent an entire column of the worksheet. You can access the Columns collection through the Sheet.Columns object. Example:

' Hide the column C (= third column) in the worksheet

pm.ActiveSheet.Columns(3).Hidden = True

No matter how you access the Range object, you can apply all the properties and methods described below.

 3  Properties, objects, collections and methods

Properties:

Item (default property)

Row R/O

Column R/O

Name

Formula

Value

Value2

HorizontalAlignment

VerticalAlignment

WrapText

LeftPadding

RightPadding

TopPadding

BottomPadding

MergeCells

Orientation

VerticalText

PageBreakCol

PageBreakRow

Comment

Locked

FormulaHidden

CellHidden

Nonprintable

Hidden

RowHeight

ColumnWidth

 

Objects:

Cells Range

Range Range

Workbook Workbook

Sheet Sheet

NumberFormatting NumberFormatting

Font Font

Shading Shading

Validation Validation

Application Application

Parent Sheet

 

Collections:

Borders Borders

 

Methods:

AutoFit

ApplyFormatting

Select

Copy

Cut

Paste

Insert

Delete

Clear

ClearContents

ClearFormats

ClearConditionalFormatting

ClearComments

ClearInputValidation

AutoFilter

Item (property, R/O)

Data type: Object

Returns a Range object that consists of just one individual cell of the calling Range object. You can use it to address each cell of the calling Range object individually.

Syntax:

 Item(RowIndex, ColumnIndex)

Parameters:

RowIndex (Type: Long) indicates the row number of the desired cell (as an offset from the top left cell in the range).
ColumnIndex (optional; Type: Long) indicates the column number of the desired cell (as an offset from the top left cell in the range).

Examples:

' Fill the first cell of the Range object with the value 42

pm.ActiveSheet.Range("B5:B10").Item(1, 1).Value = 42

 

' Shorter, as Item is the default property of the Range object

pm.ActiveSheet.Range("B5:B10")(1, 1).Value = 42

 

' Change the format of the first cell of the current selection

pm.ActiveSheet.Selection.Item(1, 1).Font.Size = 24

 

' Shorter again, using the default property

pm.ActiveSheet.Selection(1, 1).Font.Size = 24

Row (property, R/O)

Data type: Long

Returns the row number of the top row in the given range.

If multiple ranges are selected, the value for the first selected range will be returned.

Column (property, R/O)

Data type: Long

Returns the column number of the left-most column in the given range.

If multiple ranges are selected, the value for the first selected range will be returned.

Name (property)

Data type: String

Gets or sets the name of the range. Similar to the commands of the ribbon tab Formula | Named areas group, you can use it to set up and read named areas.

Formula (property)        

Data type: String

Gets or sets the formulas of the cells in the range.

Example:

' Enter the same formula for the cells A1, A2, B1 and B2

pm.ActiveSheet.Range("A1:B2").Formula = "=CHAR(64)"

Note: If the formula doesn't start with "=" or "+", it will be entered as a literal value (number, string or date).

Value (property)

Data type: String

Gets or sets the values of the cells in the range. Dates will be interpreted as a string (see also the property Value2 below).

Example:

' In Zellen A1, A2, B1 und B2 den Wert 42 eintragen

pm.ActiveSheet.Range("A1:B2").Value = 42

Value2 (property)

Data type: String

Gets or sets the values of the cells in the range. Dates will be interpreted as a number.

The difference between Formula, Value und Value2

To get or set the content of cells, you can use any of the three properties described above: Formula, Value or Value2. The difference:

If the cell contains a calculation, Formula returns the formula text, for example, "=ABS(A1)".

Value and Value2, on the other hand, always return the result of the calculation. They only differ in the interpretation of date values: while Value returns a string, Value2 returns the serial date number.

HorizontalAlignment (property)

Data type: Long (PmHAlign)

Gets or sets the horizontal alignment of the cells in the range. The possible values are:

pmHAlignGeneral                = 0 ' Default

pmHAlignLeft                   = 1 ' Left

pmHAlignRight                  = 2 ' Right

pmHAlignCenter                 = 3 ' Centered

pmHAlignJustify                = 4 ' Justified

pmHAlignCenterAcrossSelection  = 5 ' Centered across columns

VerticalAlignment (property)

Data type: Long (PmVAlign)

Gets or sets the vertical alignment of the cells in the range. The possible values are:

pmVAlignTop      = 0 ' Top

pmVAlignCenter   = 1 ' Centered

pmVAlignBottom   = 2 ' Bottom

pmVAlignJustify  = 3 ' Vertically justified

WrapText (property)

Data type: Long

Gets or sets the "Line break" setting for the cells in the range, analogous to the Line break option on the ribbon tab Home | Alignment group.

LeftPadding (property)

Data type: Single

Gets or sets the left inner margin of the cells, measured in points (1 point corresponds to 1/72 inches).

RightPadding (property)

Data type: Single

Gets or sets the right inner margin of the cells, measured in points (1 point corresponds to 1/72 inches).

TopPadding (property)

Data type: Single

Gets or sets the top inner margin of the cells, measured in points (1 point corresponds to 1/72 inches).

BottomPadding (property)

Data type: Single

Gets or sets the bottom inner margin of the cells, measured in points (1 point corresponds to 1/72 inches).

MergeCells (property)

Data type: Long

Gets or sets the setting "Merge cells", analogous to the option Merge cells on the ribbon tab Home | Alignment group. All cells in the range are connected to form a large cell (True), or the cell connection is removed again (False).

Orientation (property)

Data type: Long

Gets or sets the print orientation of the cells in the range. Possible values: 0, 90, 180 and -90 corresponding to the respective rotation angle.

Note: The value 270 will be automatically converted to -90.

VerticalText (property)

Data type: Long

Gets or sets the setting "Vertical text".

Corresponds to the option Vertical text on the Alignment tab of the dialog box for the cell properties.

PageBreakCol (property)

Data type: Boolean

Gets or sets the setting whether a page break should be performed to the left of the range.

If you set this property to True, a vertical page break will be performed between the range and the column to the left of it. If you set it to False, the break will be removed again.

Corresponds to the ribbon command Layout | Page setup group | Page break | Insert before column.

PageBreakRow (property)

Data type: Boolean

Gets or sets the setting whether a page break should be performed above the range.

If you set this property to True, a horizontal page break will be performed above the range. If you set it to False, the break will be removed again.

Corresponds to the ribbon command Layout | Page setup group | Page break | Insert before row.

Comment (property)

Data type: String

Gets or sets the comment for the cells in the range. For getting the value, if the comments are different or no comments are present, an empty string will be returned.

Corresponds to the comments that can be created and edited in PlanMaker with the ribbon command Insert | Comment.

Locked (property)

Data type: Long

Gets or sets the "Cell protection" setting, corresponding to the option of the same name on the Protection tab of the dialog box for the cell properties.

FormulaHidden (property)

Data type: Long

Gets or sets the "Hide formula" setting, corresponding to the option of the same name on the Protection tab of the dialog box for the cell properties.

CellHidden (property)

Data type: Long

Gets or sets the "Hide cell" setting, corresponding to the option of the same name on the Protection tab of the dialog box for the cell properties.

Nonprintable (property)

Data type: Long

Gets or sets the "Do not print cell" setting, corresponding to the option of the same name on the Protection tab of the dialog box for the cell properties.

Hidden (property)

Data type: Long

Gets or sets the setting whether complete columns or rows are hidden, analogous to the ribbon commands Home | Cells group | Visibility | Hide columns and Hide rows.

The area must designate one or more whole rows or columns. Some examples:

To reference column A, use the notation A:A.

To reference the columns from A to C, use the notation A:C.

To reference row 3, use the notation 3:3.

To reference the rows 3 to 7, use the notation 3:7.

Examples:

' Hide the column A

pm.ActiveSheet.Range("A:A").Hidden = True

 

' Hide the columns A, B and C

pm.ActiveSheet.Range("A:C").Hidden = True

 

' Hide the row 3

pm.ActiveSheet.Range("3:3").Hidden = True

 

' Hide the rows from 3 to 7

pm.ActiveSheet.Range("3:7").Hidden = True

Whole rows can also be addressed through the Rows collection and whole columns through the Columns collection:

' Hide the column A (= the first column)

pm.ActiveSheet.Columns(1).Hidden = True

 

' Hide the row 3

pm.ActiveSheet.Rows(3).Hidden = True

RowHeight (property)

Data type: Long

Gets or sets the row height in points (1 point corresponds to 1/72 inches).

The specified range must contain one or more entire rows or columns. For more information, see the notes on the Hidden property.

ColumnWidth (property)        

Data type: Long

Gets or sets the column width in points (1 point corresponds to 1/72 inches).

The specified range must contain one or more entire columns. For more information, see the notes on the Hidden property.

Cells (pointer to object)

Data type: Object

Returns a Range object whose elements correspond exactly to those of the source area. This allows you to address the individual cells of an area using loop variables. Example:

' Fill all cells of the range with values

Dim row, col as Integer

Dim rng as Object

 

Set rng = pm.ActiveSheet.Range("A1:F50")

For row = 1 To rng.Rows.Count

 For col = 1 to rng.Columns.Count

                 rng.Cells.Item(row, col).Value = 42

 Next col

Next row

Range (pointer to object)

Data type: Object

Returns a Range object matching the specified parameters. You can use this to construct a "sub-range" for a range and get or set the values for it, for example

Note: Please note that you have to use relative cell addressing here. For example, if you pass the cell address B2 as a parameter, it does not specify the cell with the absolute coordinates B2, but the cell that is located in the second row and second column of the range (see example).

Syntax 1:

 obj = Range(Cell1)

Syntax 2:

 obj = Range(Cell1, Cell2)

Parameters:

Cell1 (type: String) specifies either according to syntax 1 a cell range (then Cell2 must be omitted) or according to syntax 2 the upper left corner of a range (then parameter Cell2 specifies the lower right corner of the range).
Cell2 (optional; type: String) should be used only if Cell1 refers to an individual cell.
Examples for syntax 1:

 Range("A1:B20")        ' Cells A1 to B20

 Range("A1")                ' Only cell A1

 Range("A:A")                ' The whole column A

 Range("3:3")                ' The whole row 3

 Range("Summer")        ' Range labeled "Summer"

Example for syntax 2:

 Range("A1", "B20")        ' Cells A1 to B20

Example:

' Selects the cell D4

pm.ActiveSheet.Range("B2:F20").Range("C3:C3").Select

Workbook (pointer to object)

Data type: Object

Returns the Workbook object that you can use to access the workbook (= document) assigned to the range.

Sheet (pointer to object)

Data type: Object

Returns the Sheet object that you can use to access the worksheet belonging to the range.

NumberFormatting (pointer to object)

Data type: Object

Returns the NumberFormatting object that you can use to access the number formatting of the cells in the range.

Font (pointer to object)

Data type: Object

Returns the Font object that you can use to access the character formatting of the cells in the range.

Shading (pointer to object)

Data type: Object

Returns the Shading object that you can use to access the shading of the cells in the range.

Validation (pointer to object)

Data type: Object

Returns the Validation object that you can use to access the input validation in the range.

Application (pointer to object)

Data type: Object

Returns the Application object.

Parent (pointer to object)

Data type: Object

Returns the parent object, i.e. an object of the type Sheet.

Borders (pointer to collection)

Data type: Object

Returns a Borders collection representing the four border lines of the cells in the range. You can use this collection to retrieve and change the line settings (thickness, color, etc.).

AutoFit (method)

Set the row(s) or column(s) to optimal height or width, respectively. Corresponds to the ribbon commands Layout | Row group | Optimal height and Optimal width.

The given range must cover entire rows or columns.

Syntax:

 AutoFit

Parameters:

none

Return type:

none

Examples:

' Set the column A to optimal width

pm.ActiveSheet.Range("A:A").AutoFit

 

' Set the columns A, B and C to optimal width

pm.ActiveSheet.Range("A:C").AutoFit

 

' Set the row 3 to optimal width

pm.ActiveSheet.Range("3:3").AutoFit

 

' Set the rows from 3 to 7 to optimal width

pm.ActiveSheet.Range("3:7").AutoFit

 

' Set the column A (= the first column) to optimal width

pm.ActiveSheet.Columns(1).AutoFit

 

' Set the row 3 to optimal width

pm.ActiveSheet.Rows(3).AutoFit

ApplyFormatting (method)

Usually, PlanMaker executes formatting commands instantaneously.

However, if you want to apply multiple formatting changes consecutively to an individual range, you can accelerate their execution by setting the worksheet property ManualApply (see the Workbook object) to True.

In this case, you are responsible for notifying PlanMaker when you finish issuing formatting commands. To do this, enclose the formatting commands in a With structure and indicate their end using the ApplyFormatting method (see example).

Syntax:

 ApplyFormatting

Parameters:

none

Return type:

none

An example using automatic formatting:

Sub Main

 Dim pm as Object

 

 Set pm = CreateObject("PlanMaker.Application")

 pm.Visible = True

 

 With pm.ActiveSheet.Range("A1:C3")

         .Font.Name = "Arial"

                 .Font.Size = 14

                 .Font.Bold = True

                 .NumberFormatting.Type = pmNumberPercentage

                 .NumberFormatting.Digits = 2

 End With

 

 Set pm = Nothing

End Sub

An example using manual formatting:

Sub Main

 Dim pm as Object

 

 Set pm = CreateObject("PlanMaker.Application")

 pm.Visible = True

 

 pm.ActiveWorkbook.ManualApply = True

 With pm.ActiveSheet.Range("A1:C3")

         .Font.Name = "Arial"

                 .Font.Size = 14

                 .Font.Bold = True

                 .NumberFormatting.Type = pmNumberPercentage

                 .NumberFormatting.Digits = 2

                 .ApplyFormatting

 End With

 pm.ActiveWorkbook.ManualApply = False

 

 Set pm = Nothing

End Sub

Select (method)

Selects the range specified by the Range command.

Syntax:

 Select [Add]

Parameters:

Add (optional; type: Boolean): If False or omitted, the new selection replaces the existing one. Otherwise, the new selection will be added to the old one.

Return type:

none

Examples:

' Select the range B2:D4

pm.ActiveSheet.Range("B2:D4").Select

 

' Extend the current selection by the range F6:F10

pm.ActiveSheet.Range("F6:F10").Select True

Deselecting: If you would like to remove any existing selection, simply select a range consisting of only one cell:

' Set the cell frame into cell A1 (without selecting it)

pm.ActiveSheet.Range("A1").Select

Copy (method)

Copies the cells of a range to the clipboard.

Syntax:

 Copy

Parameters:

none

Return type:

none

Cut (method)

Cuts the cells of a range to the clipboard.

Syntax:

 Cut

Parameters:

none

Return type:

none

Paste (method)

Pastes the content of the clipboard to the range. If the range consists of more than one cell, the content of the clipboard will be cut or extended so that it exactly matches the range.

Syntax:

 Paste

Parameters:

none

Return type:

none

Insert (method)

Inserts an empty cell area sized equally to the range defined by Range.

PlanMaker behaves as if you had selected the range and then invoked the commands of the ribbon tab Home | Cells group | Insert.

Syntax:

 Insert [Shift]

Parameters:

Shift (optional; type: Long or PmInsertShiftDirection): Indicates in which direction the existing cells will be moved. The possible values are:

 pmShiftDown = 0 ' Downwards

 pmShiftToRight = 1 ' To the right

If this parameter is omitted, the value pmShiftDown is taken.

Return type:

none

Delete (method)

Deletes all cells from the range defined by Range. The rest of the cells in the table are shifted accordingly to fill the gap.

PlanMaker behaves as if you had selected the range and then select the commands of the ribbon tab Home | group Cells | Delete.

Syntax:

 Delete [Shift]

Parameters:

Shift (optional; type: Long or PmDeleteShiftDirection): Indicates in which direction the existing cells will be moved. The possible values are:

 pmShiftUp      = 0 ' Upwards

 pmShiftToLeft  = 1 ' To the left

If this parameter is omitted, the value pmShiftUp is taken.

Return type:

none

Clear (method)

Deletes all contents and formatting of all cells in the range defined by Range.

Syntax:

 Clear

Parameters:

none

Return type:

none

ClearContents (method)

Deletes the contents of all cells in the range defined by Range. Their formatting is retained.

Syntax:

 ClearContents

Parameters:

none

Return type:

none

ClearFormats (method)

Deletes the formatting of all cells in the range defined by Range (except for conditional formatting). Their cell contents are retained.

Syntax:

 ClearFormats

Parameters:

none

Return type:

none

ClearConditionalFormatting (method)

Deletes the conditional formatting of all cells in the range defined by Range. Their cell contents are retained.

Syntax:

 ClearConditionalFormatting

Parameters:

none

Return type:

none

ClearComments (method)

Deletes all comments in the range defined by Range.

Syntax:

 ClearComments

Parameters:

none

Return type:

none

ClearInputValidation (method)

Removes all input validation settings in the range defined by Range.

Syntax:

 ClearInputValidation

Parameters:

none

Return type:

none

AutoFilter (method)

Activates, deactivates or configures an AutoFilter for the range.

Syntax:

 AutoFilter [Field], [Criteria1], [Operator], [Criteria2], [VisibleDropDown]

Parameters:

Note: If you do not indicate any parameter, any existing AutoFilter for the given range will be switched off (see examples below).
Field (optional; type: Long) indicates the number of the column inside the AutoFilter area after which want to filter the data. If you omit this parameter, the number 1 (i.e., the first column) will be assumed.
Criteria1 (optional; type: Variant) indicates the criterion of the filter – for example "red" if you want to filter for the value "red", or ">3" to filter for values greater than three. Exception: If one of the operators pmTop10Items, pmTop10Percent, pmBottom10Items or pmBottom10Percent is used, then Criteria1 contains a numeric value indicating how many values to display. If you omit the Criteria1 parameter, all rows will be shown.
Operator (optional; type: Long or PmAutoFilterOperator) specifies the type of filtering:

 pmAll             = 0 ' Show all rows (i.e., do not filter)

 pmAnd             = 1 ' Criteria1 and Criteria2 must be met.

 pmBottom10Items   = 2 ' Only the n cells with the lowest values*

 pmBottom10Percent = 3 ' Show only the bottom n percent values*

 pmOr              = 4 ' Criteria1 or Criteria2 must be met.

 pmTop10Items      = 5 ' Show only the n highest values*

 pmTop10Percent    = 6 ' Show only the top n percent values*

 pmBlank           = 7 ' Only blank rows

 pmNonblank        = 8 ' Only non-blank rows

* In these cases, Criteria1 must contain the value for "n".
Criteria2 (optional; type: Variant) allows you to specify a second filter term. This is only possible with the operators pmAnd and pmOr.
VisibleDropDown (optional; type: Boolean) allows you to indicate whether drop-down arrows should be shown for the filter (True) or not (False). If you omit this parameter, the value True is taken.

Return type:

none

Examples:

pm.Application.ActiveSheet.Range("A1:D10").AutoFilter 1, pmTop10Items, 5 instructs PlanMaker to display only the first 5 items from the column A1.
If you do not specify any parameters, any existing AutoFilter for the given range will be switched off. Example:
pm.ActiveSheet.Range("A1:D10").AutoFilter disables the above AutoFilter.