How to Work with Ranges and Cells in VBA (Refer to RANGE Object)

HomeTop 15 Excel VBA Tutorials – Best Way to Learn VBA in Excel (Guide)CHAPTER 8: Working with Ranges and Cells in VBA – RANGE Object


What is the Range Object

When you look around in an Excel workbook, you will find that everything works around cells. A cell and a range of cells are where you store your data and then everything starts.

cells and ranges in a worksheet

To make the best of VBA you need to learn how to use cells and ranges in your codes.

For this, it’s really important for you to have a solid understanding of Range Object. By using it you can refer use cells in your codes in the following ways:

  • A single cell.
  • A range of cells
  • A row or a column
  • A three-dimensional range

The RANGE OBJECT is a part of Excel’s object hierarchy: Application ➜ Workbooks ➜ Worksheets ➜ Range and besides inside the worksheet. So if you are writing code to refer to the RANGE object it would be like this:

range object
Application.Workbook(“Workbook-Name”).Worksheets(“Sheet-Name”).Range

By referring to a cell or range of cells you can do the following things:

  • You can read the value from it.
  • You can enter a value in it
  • And, you can make changes to the format.

To do all these things you need to learn to refer a cell or a range of cells and in the next section of this chapter you will learn to refer to a cell using different ways.

...so let’s get started.

How to Refer to a Range in VBA

To refer to a cell or a range of cells you can use three different ways.

Well, which one is best out of these depends on your requirement but it is worth to learn all three so that you can choose which one is perfect for you.

  • Range Property
  • Cells Property
  • Offset Property

Out these ways which one is perfect to use depends on the situation you deal with in your worksheet.

Range Property

Range property is the most common and popular way to refer to a range in your VBA codes. With Range property, you simply need to refer to the cell address, let me tell you the syntax.

expression.range(address)

Here the expression is a variable representing a VBA object. So if you need to refer to the cell A1, the line of code you need to write would be:

Application.Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)
reference to a range object

Above code tells VBA that you are referring to the cell A1 which is in the worksheet “Sheet1 which is in the workbook “Book1”.

Note
Every time when you type a cell address in the range object make sure to wrap it in the double quotation marks.

But here’s one the thing to understand. As you are using VBA in Excel there’s no need to use the word “Application”. So the code would be:

Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

And if you are in the Book1 there you can further trim down your code:

Worksheets(“Sheet1”).Range(“A1”)

But if you are already in the worksheet “Sheet1” then you can further trim down your code and can only use:

But if you are already in the worksheet “Sheet1” then you can further trim down your code and can only use:

Range(“A1”)

Now, let’s say if you want to refer to a full range of cells (i.e. multiple cells) you need to write the code in the below way:

Now, let’s say if you want to refer to a full range of cells (i.e. multiple cells) you need to write the code in the below way:

Range(“A1:A5”)

In the above code, you have referred to the range A1 to A5 which consists the five cells.

You can also refer to a named range in using range object. Let’s say you have named range with the name of “Sales Discount” to refer to this you can write a code like this:

Range(“Sales Discount”)

If you want to refer to a non-continues range then you need to do something like this:

Range("A1:B5,D5:G10")

And if you want to refer to an entire row or a column then you need to enter code like below:

Range(“1:1”)
Range(“A:A”)

At this point, you have a clear understanding of how to refer to a cell and range of cells. But to make it best with this you need to learn how to use this to do other things.

Let’s try a few things:

1. Select and Activate a Cell using the RANGE Property

If you want to select a cell then you can use Range.Select method.

Let’s say if you want to activate the cell A5 then all you need to do is specify the range and then add “.Select” after that.

Range(“A1”).Select

This code tells VBA to select the cell A5 and if you want to select a range of cells then you just need to refer to that range and simply add “.Select” after that.

Range(“A1:A5”).Select
select a range using range object

There’s also another method which you can use to activate a cell.

Range(“A1”).Activate

Here you need to remember that you can activate only one cell at a time.

Even if you specify a range with “.Activate” method, it will select that range but the active cell will be the first cell of the range.

2. Enter a Value in a Cell using Range Property

By using the range property you can enter a value in a cell or a range of cells. Let’s understand how it works using a simple example:

Range("A1").Value = "ExcelChamps"
enter a value in a cell

In the above example, you have specified the A1 as a range and after that, you have added “.Value” which tell VBA to access the value property of the cell.

The next thing you have is the equals to sign and then the value which you want to enter (you need to use double quotation marks if you are entering a text value).

For a number, the code would like this:

Range("A1").Value = 9988

And if you want to enter a value into a range of cells, I mean multiple cells, then all you need to do is specify that range.

Range("A1:A5").Value = "ExcelChamps"

And, here’s the code if you are referring to the non-continues range.

Range("A1:A5 , E2:E3").Value = "ExcelChamps"

3. Copy and Paste a Cell/Range

With Range property, you can use “.Copy” method to copy and cell and then paste it into a destination cell.

Let’s say if you need to copy the cell A5 the code for this would be:

Range("A5").Copy
copy a range

When you run this code it will simply copy the cell A5 but the next thing is to paste this copied cell to a destination cell.

For this, you need to add the keyword Destination after it and followed by the cell where you want to paste it.

So if you want to copy the cell A1 and then want to paste it to the cell E5, the code would be:

Range("A1").Copy Destination:=Range("E5")
copy and paste a range

In the same way, if you are dealing with a range of multiple cells then the code would be like:

Range("A1:A5").Copy Destination:=Range("E5:E9")

If you have copied a range of cells and then if you have mentioned one cell as the destination range, VBA will copy entire copied range into the starting from the cell you have specified as a destination.

Range("A1:A5").Copy Destination:=Range("B1")

When you run the above code, VBA will copy range A1:A5 and will paste it to the B1:B5 even though you have mentioned only B1 as destination range.

Tip
Just like “.Copy” method you can use “.Cut” method to cut a cell and then simply use a destination to paste it.

4. Use Font Property with Range Property

With range property, you can access the font object which of a cell which helps you to change all the font settings.

There are a total of 18 different properties for the font which you can access. Let’s say if you want to make the text BOLD in cell A1, the code would be:

Range("A1").Font.Bold = True

This code tells VBA to access the BOLD property of the font which is inside the range A1 and you have set this property to TRUE.

Let’s say you want to apply strike-through to the cell A1, this time the code would be:

Range("A1").Font.Strikethrough = True
apply strike through to a cell

As I said there are a total of 18 different properties you can use, so make sure to check out all of these to see which one is useful for you.

5. Clear Formatting from a Cell using Range Property

By using “.ClearFormats” method you can remove only the format from a cell or a range of cells.

All you need to do is add “.ClearFormat” after specifying the range, like below:

Range("A1").ClearFormats

When you run above code it clear all the formatting from the cell A1 and if you want to do it for an entire range, you know what to do, Right?

When you run above code it clear all the formatting from the cell A1 and if you want to do it for an entire range, you know what to do, Right?

Range("A1:A5").ClearFormats

Now the above code will simply remove the formatting from the range A1 to A5.

How to use Cells Property

Apart from RANGE property, you can use Cells property to refer to a cell or a range of cell in your worksheet.

In cell property, instead of using the cell reference you need to enter the column number and row number of the cell.

expression.Cells(Row_Number, Column_Number)

Here the expression is a VBA object and Row_Number is the row number of the cell and Column_Number is the column of the cell.

So if you want to refer to the cell A5 you can use the code the below code:

Cells(5,1)
using cell property

Now, this code tells VBA to refer to the cell which is at row number five and at the column number one.

As it’s syntax say you need to enter column number as address but the reality is you can also use the column alphabet if you want just by wrapping it in double quotation marks.

Below code will also refer to the cell A5:

Now, this code tells VBA to refer to the cell which is at row number five and at the column number one.

As it’s syntax say you need to enter column number as address but the reality is you can also use the column alphabet if you want just by wrapping it in double quotation marks.

Below code will also refer to the cell A5:

Cells(5,”A”)

And to VBA to select it simply add “.Select” at the end.

Cells(5,1).Select
select a cell using cell property

Above code will select the cell A5 which is in the 5th row and in the first column of the worksheet.

How to use OFFSET Property

If you to play well with ranges in VBA you need to know how to use OFFSET property. It helps to refer to a cell that is a particular number of rows and columns away from another cell.

Let’s say your active cell is B5 right now and you want to navigate to the cell which is 3 column right and 1 row down from B5, you can do this OFFSET.

Below is the syntax which you need to use for the OFFSET:

expression.Offset (RowOffset, ColumnOffset)
  • RowOffset: In this argument, you need to specify a number which will tell VBA how many rows you want to navigate. A positive number defines a row downward and a negative number defines a row upward.
  • ColumnOffset: In this argument, you need to specify a number which will tell VBA how many columns you want to navigate. A positive number defines a column to the right and a negative number defines a left.

Let’s write a simple code for example which we have discussed above.

  • First of all, you need to define the range from where you want to navigate and so type the below code:
    Range(B5)
  • After that, type “.offset” and enter opening parentheses, just like below:
    Range (B5).Offset(
  • Next, you need to enter the row number and then the column number where you want to navigate.
    Range(B5).Offset(1, 3)
  • In the end, you need to add “.select” to tell VBA to select the cell where you want to navigate.
    Range("B5").Offset(1, 3).Select
navigate to a cell using offset property

So when you run this code it will select the cell which is one row down and 3 columns right from the cell B5.

How to Select a Range after Navigating to a Cell using OFFSET Property

OFFSET not only allows you to navigate to a cell but you can also resize the range further. Let’s continue the above example.

Range("B5").Offset(1, 3).Select

The above code navigates you to the cell E6 and now let’s say you need to select the range of cells which consists the five columns and 3 rows from the E6.

So what you need to do is after using OFFSET use the resize property by adding “.Resize”.

Range("B5").Offset(1, 3).Resize

Now you need to enter the row size and column size.

Type a starting parenthesis and enter the number to define the row size and then a number to define the column size.

Range("B5").Offset(1, 3).Resize(3,5)

Now in the end, simply add “.Select” to tell VBA to select the range and when you run this code it will select the range.

Range("B5").Offset(1, 3).Resize(3, 5).Select

So when you run this code it will select the range E6 to I8.

resize the selection using offset property

There's More

Below are some of the external links which could be useful for you to learn more about using ranges and cells in your VBA coder, make sure to check out all of these...

© ExcelChamps 2019
Insert Content Template or Symbol