Excel – VBA – Range

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 [gs 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 [gs macro]s.

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

Range("A1:A4").Value = 2

Result when you click the command button on the sheet:

Range("A1:B4").Value = 5

Result:

Range("A1:A2,B3:C4").Value = 10

Result:

[tab:Declare a Range]

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 [gs worksheet] and add the following code lines:

Dim example As Range



Set example = Range("A1:D1")


example.Value = 8

Result when you click the command button on the sheet:

[tab:Select a Range]

Select a Range

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

Dim example As Range


Set example = Range("A1:C4")


example.Select

Result:

[tab:Rows]

Rows

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

Dim example As Range


Set example = Range("A1:C4")


example.Rows(3).Select

Result:

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

[tab:Columns]

Columns

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

Dim example As Range


Set example = Range("A1:C4")


example.Columns(2).Select

Result:

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

[tab:Copy and Paste]

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 [gs worksheet]. The following [gs macro] copies Range("A1:A2") and pastes it into Range("C4:C5").

Range("A1:A2").Select


Selection.Copy


Range("C4").Select


ActiveSheet.Paste

Result:

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

Range("C4:C5").Value = Range("A1:A2").Value

[tab:Clear a Range]

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.

Range("A1").ClearContents

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

[tab:Count]

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.

Dim example As Range


Set example = Range("A1:C4")


MsgBox example.Count

Result:

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

Dim example As Range


Set example = Range("A1:C4")


MsgBox example.Rows.Count

Result:

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

[tab:END]

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