The VBA Range Object represents a cell or multiple cells in your Excel worksheet. Properties and Methods of the Range Object are used to manipulate cell values, change formatting and return attributes.
A Property stores information about the object, such as its address ($A$1, for example), its column number, and even its font name. These object properties can be assigned to other variables or can be set to new values.
A Method, on the other hand, is an action the object can perform. The Range Object can perform many actions, including being selected, cleared, copied and sorted.
In this tutorial, I’ll introduce you to a few Range Object Properties and Methods. Of course, Chapter 2 is titled “Getting Around,” so it’s only fitting that I focus on navigating your spreadsheet with the Range Object.
The VBA Range Object accepts two arguments: Cell1 and Cell2. The second argument is optional, as denoted by the brackets, [ ], in the following image:
You can pass an address to the Range Object with a string or with the Cells property. Standard address string notation is quite similar to how you would reference a cell in Excel. “B3” or “$B$3” would denote the third row, second column. “A1:B3” or “$A$1:$B$3” would represent the entire range from the first row, first column to the third row, second column.
The easiest way to learn is by playing around, so let’s start playing around. Open up the Visual Basic Editor and create a macro titled RangeDemo(). You’ll see the following pairs are equivalent:
The Range.Count Property returns the size of the complete range, 12. The Rows.Count Property returns the number of rows in the range, 4. The Columns.Count Property returns the number of columns in the range, 3.
If you’re looking for the last used cell in a range, I encourage you to read about the Range.End Property.
VBA Range Methods
Copy and Paste
There are many ways to copy and paste in VBA, many of which start with the Copy Property.
This example takes a range from one location, moves it to another and clears the first range. The ClearContents Method erases the values in a range, while the Clear Method erases the values and the formatting.
The PrintOut Method will print your desired range to your default printer. Altenerately, the PrintPreview Method will pull up the print preview of what your range would look like if you chose to print it.
In this lesson you learned how to control your spreadsheet with the Range Object. You learned many Range Properties, including the Value, Formula, Address and Count Properties. You also learned many Range Methods, including the Copy, ClearContents and Printout Methods.
We didn’t touch much on the Select Method in this lesson. The Select Method is important enough that we’re dedicating the entire next lesson toward it. I hope to see you there.