## Chapter 2.1 - VBA Range Object

##### Excel VBA Tutorials

Arguments | Cells | Named Ranges | Declaring a Range | Value | Formula | Address | Count | Copy | ClearContents | PrintOut

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.

**Range Arguments**

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

```
Sub RangeDemo()
Range("A1")=7,
Range(Cells(1, 1), Cells(1, 1))=7
End Sub
```

As are:

```
Sub RangeDemo()
Range("A1:C4") = 9
Range(Cells(1, 1), Cells(4, 3)) = 9
End Sub
```

**VBA Range.Cells Property**

You’ll notice the second item in each of the previous examples references an argument called Cells. The Cells property, or **Range.Cells property**, accepts two arguments: Row first, Column second.

Each argument must be an integer (a whole number). Specifically, each argument must be an integer greater than 0 since Excel row numbers and column numbers start from 1.

If you’re using the Cells Property to define your range, you’ll need to enter both arguments in the Range Object (Cell1 and Cell2). If you’re using a string, you really only need one.

Although defining your range with the Cells Property is longer, you’ll find it useful when you start looping through ranges in Excel *(More on this in a future chapter)*.

**Named Ranges**

Let’s say you have a range named

```
Sub RangeDemo()
Range("DisneyParks") = "Awesome"
End Sub
```

**Declaring a Range**

Whether for simplicity or convenience, sometimes you want to define a range in VBA.

```
Sub RangeDemo()
Dim rng As Range
Set rng = Range("A1:C4")
rng.Value = "Range"
End Sub
```

**Result:**

Later, we’ll dedicate an entire lesson to the Dim Statement.

**VBA Range Properties**

#### Value Property

Although it was optional, I used the **Value Property** in my **Declaring a Range** example, earlier. Let’s see another example. Say you have a value in one cell that you want to repeat in another range:

Open a worksheet and type a “3” in cell “A1.”

```
Sub RangeDemo()
Range("C1:D5") = Range("A1").Value
End Sub
```

**Result:**

#### Formula Property

```
Sub RangeDemo()
Range("C1:D5") = Range("A1").Value
Range("B5").Formula = "=Sum(C1:D5)"
MsgBox (Range("B5").Formula & vbNewLine & Range("B5").Value)
End Sub
```

**Result:**

You can see the **Formula Property** in the `MsgBox`

returns a string that shows the formula, while the Value Property returns the output of the formula.

#### Address Property

```
Sub RangeDemo()
MsgBox (Range("C1:D5").Address)
End Sub
```

**Result:**

In this example, the **Address Property** returns a string representing the location of the range. This is useful if working with Named Ranges or variable range dimensions.

#### Count Property

Want to know how big your range is? Or more useful, how many rows and how many columns your range is?

```
Sub RangeDemo()
Dim rng As Range
Set rng = Range("A1:C4")
rng.Value = "Range"
Range("A5") = rng.Count
Range("B5") = rng.Rows.Count
Range("C5") = rng.Columns.Count
End Sub
```

**Result:**

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**.

**Method 1: Range.Copy Method**

```
Sub RangeDemo()
Range("A1:B3") = 7
Range("A1:B3").Copy Destination:=Range("a5")
End Sub
```

**Result:**

**Method 2: Worksheet.Paste Method**

```
Sub RangeDemo()
Range("A1:B3").Copy
Range("A5").Select
ActiveSheet.Paste
End Sub
```

**Result:**

**Method 3: Worksheet.PasteSpecial Method**

```
Sub RangeDemo()
Range("A1:B3").Copy
Range("A5").PasteSpecial
End Sub
```

**Result:**

Notice there’s no Range.Paste method.

#### ClearContents Method

If you want to clear the contents of a range, there’s a method for that.

```
Sub RangeDemo()
Range("A1:B3") = 7
Range("A5:B7") = Range("A1:B3").Value
Range("A1:B3").ClearContents
End Sub
```

**Result:**

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.

#### PrintOut Method

Want to print a range?

```
Sub RangeDemo()
Range("A1:B3") = 7
Range("A1:B3").PrintOut
End Sub
```

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.