VBA Range (Working with Ranges and Cells) – The Guide

Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac

Introduction to Range and Cells in VBA

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.

To make the best of VBA, you need to learn how to use cells and ranges in your codes. For this, you need to have a solid understanding of Range Object. By using it, you can refer to 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:

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 to a cell or a range of cells, and in the next section of this tutorial, you will learn to refer to a cell using different ways.

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

  • Range Property
  • Cells Property
  • Offset Property

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

So let’s get started.

Quick Tips: Developer Tab | Visual Basic Editor | Run a Macro

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:

Image1

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

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

Note: Whenever you type a cell address in the range object, make sure to wrap it in the double quotation marks. But here’s one thing to understand. As you are using VBA in Excel there’s no need to use the word “Application”. So the code would be:

Image2

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

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

Image3 

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:

Image4

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 following way:

Image5

Range("A1:A5")

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

You can also refer to a named range using the 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:

Image6

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.

here we have a complete list of tutorials that you can use to learn to work with ranges and cells in VBA but now, let’s try a few things.

1. Select and Activate a Cell

If you want to select a cell then you can use the Range.Select method. Let’s say if you want to select 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

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 the “.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

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"

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

The next thing you have is the equals 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 the “.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 

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")

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 the 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 the “.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 property 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. Now, let’s say you want to apply strikethrough to the cell A1, this time the code would be:

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

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

By using the “.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 the code above it clears all the formatting from 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.

Cells Property

Apart from the RANGE property, you can use the “Cells” property to refer to a cell or a range of cells 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)

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 says 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

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

OFFSET Property

If you want 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 columns 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 that 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 that 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, you need to define the range from where you want to navigate and so type the below code:
    1-define-the-range
  • After that, type “.Offset” and enter opening parentheses, just like below:
    2-type-offset
  • Next, you need to enter the row number and then the column number where you want to navigate.
    3-enter-row-and-column-number
  • In the end, you need to add “.Select” to tell VBA to select the cell where you want to navigate.
    4-add-select-to-tell-vba

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

Resize a Range using OFFSET

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 that consists of the five columns and three 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)

In the end, 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.

More on VBA Ranges and Cells