The Range object represents a cell, column, row, or group of cells in the Excel worksheet grid. The Cells property refers to all the cells of the specified Range object.
How to reference the Range and Cells objects
Range uses the column letter and row number in quotations while Cells uses both column and row numerical values. The following expressions refer to the cell D4:
Range("D4")
or
Cells(4, 4)
which is the shorter and most commonly used version of Cells.Items(4,4)
ActiveCell refers to the unique cell active or highlighted at that moment.
To reference a range, use the colon as separator between the delimiting cells; and separate with a comma for multiple ranges as indicated below.
Range("A1:D4")
Range("A1:D4, H6:K10")
The following expression references the entire column A using the Range object. But it can also be done using the Columns property independently.
Range("A:A")
or
Columns("A")
Both Range and Cells can be declared as Range objects and defined as follows:
In all the examples above, the range refers to the active sheet. It would be the same as writing:
ActiveSheet.Range("A1:D4")
However, in some cases we need to reference a range in a particular sheet or even workbook (see worksheet and workbook VBA objects for more details).
Assign/get a value to/from a range or cell
The following examples show how to assign or get a value to or from a range or cell using the Value property:
To remove the content, we can also use the following methods:
Range("A1:D4").Cells.Clear 'it clears content and format in that range
or simply
Range("A1:D4").Clear
ActiveSheet.Cells.Clear 'it clears content and format in all cells of the active sheet
We can format the value inside a range or cell using the NumberFormat property. It accepts all Excel’s known formats as well as custom formats. Let’s see some examples:
Select/Copy/Paste a range or cell
The below examples show how to select a single cell, a range, or multiple ranges with the Select method. We can activate and cell or range(s) within the selection; if nothing is selected, Activate works as Select.
The following example shows how to copy a range to other location:
NOTE: To copy/paste a range from one sheet or workbook to another, you need to reference those objects in the statement. Have a look at the Excel Object Model in the tutorial for beginners if this is new to you. Here’s an example to copy a range from Sheet1 to Sheet2:
Sheets("Sheet1").Range("A1:D4").Copy Sheets("Sheet2").Range("A1")
Formatting text, background, and border of a range or cell
There are three main attributes (also considered as objects themselves) to format a range or cell: The text (Font), the background (Interior), and the border (Borders). The table below shows the most common formatting properties for each of these three attributes of a range or cell.
|
Attribute (or object) |
Properties |
|
Font |
Name, Size, Color or ColorIndex, FontStyle (or separately Bold, Italic, Underline) |
|
Interior |
Color or ColorIndex, Pattern |
|
Borders |
Color or ColorIndex, LineStyle, Weight |
Font name (e.g. Arial, Calibri, etc), size, and style are straight forward properties that any Excel user should understand. The FontStyle can also be applied individually with the Booleans Bold, Italic, and Underline. Let’s see how the structure looks like:
*the ‘With’ statement is explained in Chapter 3 (The Excel Object Mode) of the tutorial for beginners
The Color and ColorIndex properties can be applied to the Font, Interior, and Borders objects (and to some other VBA objects). There are three ways to add color with those two properties. The simplest is with the Excel VB color, which has a range of only 8 colours though:
Example: adding red color to the interior of Cell A1 using the VB Color property
Range(“A1”).Interior.Color = vbRed
The ColorIndex property allows for 56 basic colors (see below), while the RGB property makes possible choosing from thousands of combinations of Red, Green, and Blue, setting a value for each of the three base colours between 0 and 255.
Example: adding red color to the interior of cell A1 using ColorIndex and RGB Color properties
The Borders object has two other commonly used properties on top of those mentioned earlier for Font and Interior. These are the LineStyle and Weight.
Furthermore, Borders accept attributes to target a particular border side as it can be seen in the following example, where the border is added specifically to one of the edges of the range or cell B2:
Range("B2").Borders(xlEdgeBottom).LineStyle = xlDash
The BorderAround method of the range object is used to create a border around the whole range, and not individual cells as done before. It accepts the same attributes but follows a different structure. The following is setting a continuous red medium-weight border around the range B2:F8.
Range("B2:F8").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=3
Formatting the dimensions and arrangement of ranges and cells
Other range properties allow to change the size of each individual cell based on the column width and row height, as well as aligning the content inside.
also
Columns(1).ColumnWidth = 20
Horizontal alignment can be set to xlLeft, xlCenter, and xlRight, while vertical alignment can be set to xlTop, xlCenter, xlBottom.
also
Rows(1).HorizontalAlignment = xlCenter
The Autofit property will set the width or height of the cells to fit its contents.
We can insert rows, columns, and cells with the Insert and Delete methods respectively. The methods accept the Shift attribute to determine the move of adjacent cells. Insert accepts xlShiftToRight and xlShiftDown, while Delete accepts xlShiftToLeft and xlShiftUp.
We can also merge/unmerge cells as shown below. The Merge method set to True will merge cells for each row within the range.
The Hidden property is used to hide/unhide columns and rows as follows:
We can lock/unlock cells as shown below. Note the worksheet needs to be protected to have effect (see protect/unprotect worksheets here).
Other VBA Objects
No comments:
Post a Comment