VBA Range Cells

When we refer to any data, whether it be any cells or selection, we use the Range property of VBA. Using the Range property, we can access any part of the worksheet. It is an inbuilt property. Range cells mean using the range property for a single cell-like range. (“A1”). Here, we have referred to cell A1.

As a learner of VBA, it is very important to understand the concept of the range of cells to do any activity in the spreadsheet. In this article, we will explain the important techniques of range cells.

Syntax of RANGE object

Now, take a look at the syntax of the VBA RANGE object.

• Cell1 is nothing but what is the first cell we are referring to. For example, if we refer to cell A1, the argument will be Range(“A1”). We can also refer to multiple cells here. For example, if we are referring from A1 to A5, the code will be Range(“A1:A5”).
• this is the second cell of the range we are referring to. It is an optional argument.

We can access all its properties and methods when we reference the cell using the Range object. For example, take a look at the below image.

Code:

Sub Range_Example1()

  Range("A1").

End Sub

We can activate the cell. We can add a comment. We can copy, we can paste, etc. Many things we can do with it.

For example, if we want to select cell A1, we can use the Select method.

Code:

Sub Range_Example1()

  Range("A1").Select

End Sub

It will select cell A1 in the activate sheet.

We can also select multiple individual cells. For example, if we want to select A1, B2, and C3 cells. Then, we can use the below code.

Code:

Sub Range_Example1()

  Range("A1,B2,C3").Select

End Sub

It will select the cells like this.

We can also select a range of cells using the range object. For example, if we want to select the cells from A1 to B5, we can use the below code.

Code:

Sub Range_Example1()

  Range("A1:B5").Select

End Sub

It will select the range of cells from A1 to B5.

Example #1 - Inserting Values to Cells

Like how we have selected cells by using the RANGE object similarly, we can also insert values.

For example, if we want to insert the word “Hello” to cell A1, we can first reference the cell and use the VBA VALUE property like the one below.

To insert a value, put an equal sign and enter the value in double-quotes.

Code:

Sub Range_Example2()

  Range("A1").Value = "Hello"

End Sub

It will insert the value “Hello” to cell A1.

We can also insert the same value to multiple cells. For this, we need first to mention what those cells are. The below code is an example of inserting the word “Hello” into cells A1, B2, and C3.

Code:

Sub Range_Example2()

  Range("A1,B2,C3").Value = "Hello"

End Sub

It will insert the value “Hello” to the cells “A1, B2, and C3”.

Example #2 - Reference cells from Another Sheet

In the active worksheet, we can select the cells from another worksheet. For example, if we want to select the cells A1 to A5 from the worksheet “City List,” we can use the below code.

Code:

Sub Range_Example3()

  Worksheets("City List").Range("A1:A5").Select

End Sub

We first need to mention which worksheet we are referring to but using the WORKSHEET object, i.e., Worksheets(“City List”).

Then, we used our RANGE object to reference the cells. Here, we have used the SELECT method. But, of course, you can use any other method associated with range objects in VBA.

Example #3 - Reference Cells from another Workbook

We can also reference the range of cells from another workbook, like how we mentioned the worksheet name above. Similarly, we need to mention the workbook name and its file extension.

For example, if you want to select cells A1 to A5 from the worksheet “Sales Sheet” from the workbook “Sales File 2018,” you can use the code below.

Code:

Sub Range_Example4()

  Workbook("Sales File 2018.xlsx").Worksheets("Sales Sheet").Range("A1:A5").Select

End Sub

Firstly, we need to reference the workbook by using the Workbook property. Workbook(“Sales File 2018.xlsx”).

Then, as usual, we selected the worksheet Worksheet (“Sales Sheet”).

Then in that worksheet, the range of cells selected, i.e., Range(“A1: A5”). Select.

Example #4 - Use the word “SET” for Range Object

As we told you at the start, we can reference the range object using the word “set.” Now take a look at the below code.

Code:

Sub Range_Example5()

  Dim Rng As Range
  Set Rng = Worksheets("Sales Sheet").Range("A1:A5")

  Rng.Value = "Hello"

End Sub

The first thing we have done here is we have declared the variable “Rng” as RANGE.

Then, we used the word “Set” to set the range.

Worksheets(“Sales Sheet”).Range("A1:A5")

Now, the variable “Rng” is equal to the range A1 to A5 in the worksheet “Sales Sheet.”

Since we have already set the range of cells for the variable “Rng,” we can now access all the associated properties and methods. For example, Rng. Value = “Hello” means in cells A1 to A5 value should be “Hello.”