The Range object is probably the most used
object in Excel VBA. It is a property of a Worksheet object, so it only exists
if there is an active worksheet, which requires an active workbook. The Range
object represents a container of cells, which are the ultimate recipient for
the data in Excel.
How to reference the Range object
As we have seen earlier, we refer to a Range
object by putting the address (column letters and row numbers) surrounded by
double quotations. We can refer to several ranges using a comma separator
between each range, and we can also refer a named range by simply putting the
name in double quotations.
Range("D4")...
Range("D4:G8")...
Range("D4:G8, K12:M18")...
Range("myRange")...
We can refer to a whole row or column with the
Range object or with the Rows and Columns properties as follows:
Range("A:A")... or Columns("A")... or Columns(1)...
Range(2:2)... or Rows(2)...
Note the Rows and Columns (plural) properties
return a Range object, however, the Row and Column (singular) properties return
a number. Thus, we can get the row or column number for a given range. The
below example returns 26.
Range("Z10").Column
A very useful expression uses the Row or Column
properties to return the last row or column with content in a range. The below
example returns the number of the last row with content in the given range by
using the End and Row properties.
Range("A1:D100").End(xlDown).Row
As we have seen earlier, we need to follow the
object hierarchy to reference a Range object in other worksheet or workbook.
Otherwise, the range refers to whatever sheet and workbook is active when
running the macro.
Application.Workbooks("Book1.xlsx").Worksheets("Sheet2").Range("D4:G8")...
The Cells property/object
The Cells property of the Range object has an
Item property to refer to a single cell within a range. Generally, the Item
property is omitted and Cells is used alone with the given row and column
numbers in parenthesis.
Cells.Item(4,3)... or Cells(4,3)...
The Cells property represents a cell object
within the worksheet and is used in the same way we use Range with other sheets
or workbooks.
Workbooks("Book1").Sheets("Sheet1").Cells(4,
3)...
A range can also be referenced with Cells as
indicated below. This is handy when using variables to define row and column
numbers.
Range(Cells(1,1), Cells(4,4))...
The Offset property
The Offset property of the Range object is used
to reference a cell located a particular number of rows and columns from that
range. The below example references the cell E4.
Range("A1").Offset(3, 4)...
Other useful properties
The Count property of the Range object returns
the number of cells in a range. It can be used with the Rows and Columns properties
to show those figures respectively.
Range("A1:D8").Count 'equals
32
Range("A1:D8").Rows.Count 'equals
8
Range("A1:D8").Columns.Count 'equals
4
As we have seen earlier, the Value property is a
read-write property of the value of a cell or range (we can get or give the
value). The value can be any type of data or a predefined variable (see next lesson).
Double quotations represents an empty string value.
Range("A1").Value = 10
Range("I8").Value = "Salary"
Range("I8").Value = ""
The NumberFormat property changes the format of
the values in a cell or range. The expression follows the Excel custom format
codes. The below example changes the number format of
column B to percentage with two decimal places.
Range("B:B").NumberFormat = "0.00%"
The Formula property is used to insert formulas
in a cell or range and it takes the exact Excel formula syntax (including the
equal sign) in double quotations.
Range("A10").Formula = "=SUM(A1:A9)"
Font, Interior, and Borders properties
The Font property actually returns a Font object
that can take several other properties such as Name, Size, Color or ColorIndex,
Style, etc. See below some examples of these useful formatting properties.
With ActiveSheet.Range("A1:H20")
.Font.Name = "Arial"
.Font.Size = 12
.Font.FontStyle = "Bold Italic"
End With
The Interior property returns the Interior object
(or background) of the cell in the same way we have seen with Font. We can
change the background color or pattern of a cell or range with the Color or
ColorIndex, and Pattern properties respectively.
The Borders property represents the third part
of a cell along with Font and Interior. Some of the properties for Borders
include LineStyle, Weight, Color or ColorIndex, etc.
Color and ColorIndex properties
Now let’s see how color works in VBA. As we have
seen just above, the Color and ColorIndex properties can be applied to set the
color of the Font, Interior, and Borders objects (and of some other VBA objects
too). There are different ways to do that.
The simplest way is using Excel VB color, which
has a range of only 8 colors.
The following code sets the color of the
interior of Cell A1 to red using the VB Color property.
Range("A1").Interior.Color = vbRed
The ColorIndex property allows for 56 basic
colors (see below), while the RGB function makes possible choosing from
thousands of combinations of Red, Green, and Blue.
The below code sets the color of the interior of
cell A1 to red using the ColorIndex and RGB Color properties.
Range("A1").Interior.ColorIndex = 3
Range("J1").Interior.Color =
RGB(255, 0, 0)
Useful methods of the Range object
We can either select or activate a Range object. If a range is selected, the Activate method can active a single cell within the selection. With the Worksheet and Workbook objects it works slightly different. We can select several Worksheet objects but
can only activate one. Workbook objects can only be activated. Here’s how we
select or activate a Range:
Range("A1:D4").Select
Range("B2").Activate
Cells(4, 8).Select
Note we can only select a Range in the active
sheet. If we want to select a range in other sheet we need to first activate
it.
Sheets("Sheet2").Activate
Range("A1:D4").Select
We copy a range in the active sheet or any other
specific sheet with the Copy method. However, the Paste method only works with
a Worksheet object, so it’s better to use the PasteSpecial method instead as
that works with the destination range object.
Range("A1:D4").Copy
Range("H1").PasteSpecial
We can also use the range destination attribute
of the Copy method to do the same operation in just one line of code.
Range("A1:D4").Copy
Range("H1")
The Clear method of the Range object deletes the
content and cell formatting of a range. Alternatively, the ClearContents method
deletes only the content and the ClearFormats method the format.
Range("A1:M20").Clear
The Delete method deletes the content, format,
and cells itself, thus shifting any other cells around to fill up the gap. We
can add an argument to indicate which direction those cells around will move.
Range("E2:H10").Delete xlToLeft
No comments:
Post a Comment