|Back to Excel Homepage||Excel VBA - Reference Guide|
Range is one of the most widely used objects in Excel VBA, as it allows the manipulation of a row, column, cell or a range of cells in a spreadsheet.
When recording absolute macros, a selection of
methods and properties use this object:
Range("A1").FormulaR1C1 = 10
A generic global object known as Selection can be used to determine the current selection of a single or range cells.
When recording relative macros, a selection of
methods and properties use this object:
Selection.Font.Bold = True
There are many properties and methods that are shared between Range and Selection objects and below are some illustrations (my choice of commonly used identifiers):
Returns or sets the reference of a selection.
Use this property to detect how many ranges (non-adjacent) are selected.
The Count method returns the number selected as the Areas is a property only.
Use the Areas property to check the state of a spreadsheet. If the system detects multiple ranges, a prompt will appear.
This property can be used as an alternative to the absolute range property and is generally more flexible to work with, as variables are easier to pass into it.
There are two optional arguments:
Leaving the arguments empty (no brackets), it will detect the current selection as the active range.
Adding an argument to either row or column with a number will refer to the co-ordination of the number passed.
Adding both arguments will explicitly locate the single cell’s co-ordinate.
Variables can be passed into the Cells property and then nested into the Range object as in the following example:
By wrapping a range property around two cell
properties, the flexibility of passing variables becomes apparent.
Range(Cells(1, 1), Cells(intRows, intCols))
Column(s) and Row(s) Properties
Four properties that return the column or row number for the focused range.
The singular (Column or Row) returns the active cell’s co-ordinate and the plural (Columns or Rows) can be used to count the current selections configuration.
Selects from the active cell’s position all cells that are adjacent (known as a region) until a blank row and blank column breaks the region.
Use this statement to select a region.
Run this piece of code:
Make sure the active cell is in the region of data you wish to capture before running the above procedure.
This property is useful for extending or re-defining a new size range.
To extend this range
by one row and one column to
use the code snippet below:
Resizing a range can be increased, decreased or change the configuration (shape) by combining positive and negative values inside the Resize property’s arguments.
This property is used in many procedures as it controls references to other cells and navigation.
Two arguments are passed into this property that is
then compounded with either another property or a method.
ActiveCell.OffSet(0, -1).Value = "X"
is quicker to execute than:
The above two examples produce the same result but instead of telling Excel to move to the active cell and then enter a value, it is more efficient to refer (or point) to the resulting cell and remain in the same position.
This method should not be confused with the Select method as commonly used in VBA.
The Select method means go and navigate to it.
The Activate method selects a cell within a selection.
By default, in a selection of cells, the first (top left position) is the active cell (white cell in a block).
The above procedure selects a fixed range of cells with a message box confirming the address of the active cell. Then, using the Activate method, move the active cell to address B2.
There are six variations of this method:
Simply locate the object and use of the above methods:
CUT, COPY and PASTESPECIAL Methods
These methods simulate the windows clipboard cut, copy and paste commands.
There are a few different types of these methods where most arguments are optional and by changing the argument settings, will change the behaviour of the method.
If the copy and cut methods omit the argument Destination, the item is copied to the windows clipboard.
INSERT and DELETE Methods
These methods can add or remove cells, rows or columns and is best used with other properties to help establish which action to execute.
Using the SET Keyword Command
Users can create and set a range object instead and like all other object declarations, use the Set command (which is used for object variable declarations).
This is an alternative way of working with the range object and is sometimes preferred as it exposes more members (properties and methods).
For example, using a For...Loop (see For...Loop section about this control flow), iterating in a collection is carried out by declaring and setting a variable as a Range object:
The above procedure checks each cell in a fixed range (A1 to C10) and determines its value, placing a 0 (zero) if the cell is empty.
Tip: You can refer to
using the convention [A1]
which may be easier to write.
VBA Keywords: Range, Select, Clear, Font, Bold, Address, Selection, MsgBox, Area, Count, Cells, InputBox, CurrentRegion, OffSet, Resize, Columns, Rows, Column, Row, Dim, Activate, ClearFormats, ClearComments, ClearContents, ClearNotes, ClearOutline, Cut, Copy, PasteSpecial, Insert, Delete, EntireRow, Set, Borders, Interior, Do...Loop, For...Next and If...Then
© copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.