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