VBA Range Cells

Excel VBA Range Cells

In VBA when we refer to any data whether it be any cells or selection we use range property of VBA, using range property we can access any part of the worksheet and it is an inbuilt property, range cells means 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 to you the important techniques of Range Cells.

• You can call it as CELLS or you can call it as RANGE, there is no difference in this. In VBA we can reference the cells in two ways, one is using CELLS property and another one is using RANGE object.
• Here it is important to understand the words PROPERTY and OBJECT. It is always a confusing thing at the start.
• Using CELLS property we can reference one cell at a time, but using RANGE object we can reference multiple cells at a time in the same worksheet. Since RANGE is an object we can set the range as a particular range of cells by using the word “Set”, we will see more examples in some time now.

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 refereeing to, if we are referring to the cell A1 then the argument will be Range(“A1”). We can also refer multiple cells here, for an example if we are referring from A1 to A5 then the code will be Range(“A1:A5”)
• [Cell2] this is the second cell of the range we are referring to. This is an optional argument.

How to use Range Cells in VBA?

The moment we reference the cell by using the Range object we can access all the properties and methods associated with it. 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 the cell A1 we can use the Select method.

Code:

Sub Range_Example1()

  Range("A1").Select

End Sub

This will select the cell A1 in the activate sheet.

We can also select individual multiple cells as well, for example, if we want to select A1, B2, and C3 cell we can use the below code.

Code:

Sub Range_Example1()

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

End Sub

This 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

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

Example #1 – Inserting Values to Cells

Like how we have selected cells by using RANGE object similarly we can insert values as well.

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

Now 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

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

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

Code:

Sub Range_Example2()

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

End Sub

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

Example #2 – Reference cells from Another Sheet

Not only in the active worksheet, but we can also select the cells from another worksheet as well. 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

The first thing we need to do is we need to mention which worksheet we are referring to but using WORKSHEET object i.e.  Worksheets(“City List”).

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

Example #3 – Reference Cells from another Workbook

We can reference the range of cells from another workbook as well. Like how we have mentioned the worksheet name above similarly we need to mention the workbook name along with its file extension.

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

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 WorkbooK property. Workbook(“Sales File 2018.xlsx”).

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

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

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

As I told at the start we can reference the range object by using the word “set”. Now take 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 have 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”, now we can access all the properties and methods associated with it.  Rng. Value = “Hello” means in cells A1 to A5 value should be “Hello”.

Recommended Articles

This has been a guide to VBA Range Cells. Here we learn how to work with cells and ranges object in VBA along with practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

• VBA IIF
• Excel VBA Dictionary
• Excel VBA Tutorial for Beginners
• Select Cell in VBA