<< 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:

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:

 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")                ' Named range "Summer"

 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:

Return type:

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:

Return type:

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:

Return type:

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:

Return type:

Cut (method)

Cuts the cells of a range to the clipboard.

Syntax:

 Cut

Parameters:

Return type:

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:

Return type:

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 selected the commands of the ribbon tab Home | Cells group | Insert.

Syntax:

 Insert [Shift]

Parameters:

 pmShiftDown = 0 ' Downwards

 pmShiftToRight = 1 ' To the right

Return type:

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:

 pmShiftUp      = 0 ' Upwards

 pmShiftToLeft  = 1 ' To the left

Return type:

Clear (method)

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

Syntax:

 Clear

Parameters:

Return type:

ClearContents (method)

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

Syntax:

 ClearContents

Parameters:

Return type:

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:

Return type:

ClearConditionalFormatting (method)

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

Syntax:

 ClearConditionalFormatting

Parameters:

Return type:

ClearComments (method)

Deletes all comments in the range defined by Range.

Syntax:

 ClearComments

Parameters:

Return type:

ClearInputValidation (method)

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

Syntax:

 ClearInputValidation

Parameters:

Return type:

AutoFilter (method)

Activates, deactivates or configures an AutoFilter for the range.

Syntax:

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

Parameters:

 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

Return type:

Examples: