Range

Range Examples | Declare a Range | Select a Range | Rows | Columns | Copy/Paste a Range | Clear a Range | Count

This chapter gives an overview of the properties and methods of the very important Excel VBA Range object.

Range Examples

We have already seen the Range object in the previous chapters. The Range object is the representation of a cell (or cells) on your worksheet. The code line: 'Range("A1").value = 1' places the value 1 into cell A1. You can also execute operations in Excel Visual Basic on more than one cell at the same time. See the following three macros.

Place a command button on your worksheet and add the following code line:

Result when you click the command button on the sheet:

Code:

Result:

Code:

Result:

Declare a Range

In the Variables chapter, we learned how to declare a variable in Excel VBA. Besides declaring a variable, you can also declare an Excel VBA Range object. You can do this by using the keywords Dim and Set.

Place a command button on your worksheet and add the following code lines:

Result when you click the command button on the sheet:

 

Select a Range

An important method of the Excel VBA Range object is the Select method. The Select method simply selects a range.

Code:

Result:

Rows

The Rows property gives access to a specific row of a range. The following macro selects the third row of Range("A1:C4").

Result:

Note: Range("A1:C4") has been formatted for illustration.

Columns

The Columns property gives access to a specific column of a range. The following macro selects the second column of Range("A1:C4").

Result:

Note: Range("A1:C4") has been formatted for illustration.

Copy and Paste a Range

The Copy and Paste methods are used to copy a certain range and to paste it somewhere else on the worksheet. The following macro copies Range("A1:A2") and pastes it into Range("C4:C5").

Result:

Although this is allowed in Excel VBA, it is much easier to use the following code line which does exactly the same.

Clear a Range

To clear the content of an Excel range, you can use the Clear method. Besides emptying the range, this method also clears the format of the range. If you only want to clear the content, you can use the ClearContents method. If you only want to clear the format, you can use the ClearFormats method. The following code line clears the content of cell A1.

Note: Range("A1").ClearContents in Excel VBA is exactly the same as Range("A1").value = ""

Count

With the Count property, you can count the number of cells, rows and columns of an Excel range. Below are some examples.

The following macro counts the number of cells of the formatted range.

Result:

The following macro counts the number of rows of the formatted range.

Result:

In a similar way, you can count the number of columns of a range.

Did you find this information helpful? Show your appreciation, vote for us.

Go to Top: Range    |   Go to Next Topic: Events