The Range Object

The Range object represents a group of one or more contiguous cells in an Excel worksheet. The Range object is one level beneath the Worksheet object in Excel's object hierarchy, and it is extremely useful, as it allows us to manipulate the properties of an individual cell or collection of cells in a worksheet. You will probably find yourself using the Range object in every program you write using VBA for the Excel application.

Consider the following code examples that use properties of the Range object.

Range("A1").Value="Column A" Range("A1:G1").Columns.AutoFit Range("A1:C1", "E1:F1").Font.Bold = True

The Range object is one example of a VBA collection object that does not use the plural form of an existing object for its name. The Range object is a collection object in the sense that it represents a collection of cells in a worksheet, even if the collection represents only one cell.

First, note that a long object path is omitted from the examples above; thus, these lines of code will operate on the currently selected worksheet. The first line inserts the text Column A into cell A1 by setting its Value property. The Range property was used to return a Range object representing a single cell (A1) in this example. You have already seen several examples of the Value property in this book. Although the Value property exists for several objects, it is the Range object for which it is most commonly used. The second line of code above uses the AutoFit() method of the Range object to adjust the width of columns A through G such that the contents of row 1 will just fit into their corresponding cells without overlapping into adjacent columns. This is equivalent to the user selecting Format, Column, AutoFit Selection from the Excel application menu.

Entries in other rows that are longer than the entries in row 1 will still run into the next column. To automatically adjust the width of these columns such that the contents of every cell in the columns fit within cell boundaries, use the range A:G instead of A1:G1. The third and last example demonstrates setting the Bold property of the Font object to true for two distinct ranges in the active worksheet. The two ranges are A1:C1 and E1:F1. You are allowed to return a maximum of two ranges, so adding a third range to the arguments in the parentheses would generate a run-time error.

The examples above demonstrate just a couple of formatting methods and properties belonging to the Range object (AutoFit(), Columns, and Font). If you are a regular user of Excel, then you have probably surmised that there are numerous other properties and methods related to formatting spreadsheet cells. You can either search the Object Browser or the online help for more examples on how to use formatting options of interest; however, when you know what formatting options you want to include in your VBA program, record a macro. It is a quick and easy way to generate the code you need without having to search the documentation for descriptions of the desired objects, properties and methods. After you have recorded the macro in a separate module, you can clean up the recorded code and then cut and paste into your program as needed.

You may have noticed that the range arguments used in the examples above (A1, A1:G1, etc.) are of the same form used with cell references in the Excel application. The identical syntax is highly convenient because of its familiarity.

Finally it is time to take a closer look at the Cells property, specifically the Cells property of the Application, Range, and Worksheet objects.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment