VBA Range

Excel VBA Range Object

A range is nothing but a cell or series of cells. In VBA we often refer the word range to select a particular cell or series of cells. While working with excel everything refers to cells & ranges, it is not different in VBA as well.

In order to understand the VBA, understanding of the basics of range in VBA is very important. We usually do a lot of activities with a range of cells, for example, we copy, we paste, we edit, we delete, etc.…

Similarly in VBA too, we have a specific set of code which can perform the task for us. In this article, we will look into VBA range property.

Syntax of Range Object

Range property includes cells as its parameter.

How to Use Excel VBA Range Object?

We will learn how to use a VBA Range Object with few examples.

By using Select method – Example #1

For example, if you want to select a range of cells from A1 to A10 what will you do, you will either select through your mouse or you will use a shortcut key to select. Similarly in VBA too, we need to tell the excel what to do in writing. For example, if you want to select the cells from A1 to A13 below code will do the job for us.

Code:

Sub Range_Example()

Range("A1:A13").Select

End Sub

If you run this code using F5 key or manually, this would select the cells from A1 to A13 in the active sheet.

This code will perform the task in the active sheet. I have performed this task in Sheet 1. If I go to Sheet 2 and run this code there also it will select the cells from A1 to A13.

Like this, if you don’t mention worksheet name it will always select the supplied range in the active sheet even if it is a different workbook which is opened.

If you want to select the cells in Sheet 1 only then you need to supply this in code as I have shown below.

Code:

Sub Range_Example()

Worksheets("Sheet 1").Activate

Range("A1:A13").Select

End Sub

First, you need to activate the Worksheet name called “Sheet 1” and then you need to perform the task of selecting a range from A1 to A13. Irrespective of which sheet you are it will activate the Sheet 1 and select the range A1 to A13.

Selecting Range – Example #2

You can supply the range in the first argument itself or else you can supply it as two arguments as well. Like I have shown in the previous example we can select the range of cells from A1 to A13 by using below method as well.

Code:

Sub Range_Example2()

Worksheets("Sheet 1").Activate

Range("A1", "A13").Select

End Sub

Run this code using F5 key or manually to see the result.

This would also perform the same task as the previous one. Similarly, if you are selecting the range from different workbooks you need to specify the workbook name also.

Selecting Range – Example #3

For example, if I want to select the range of cells from A1 to A13 in the workbook called “Example WB” firstly I need to specify the workbook name by using Workbooks property and I need mention the full workbook name with its extension.

After selecting the Workbook we need to select the worksheet using Worksheet property.

Code:

Sub Range_Example3()

Workbooks("Example WB.xlsm").Worksheets("Sheet1").Activate

Range("A1", "A13").Select

End Sub

Selecting Range with End Property – Example #4

Assume you have data as shown in the below image.

If you want to select the last used cell in the column how do you select in excel? Usually, you first select the cell A1 and you will hold Ctrl key and press down arrow key & it will select the last used cell.

Similarly in VBA same logic will be applied but here you to use the END property instead regular excel Ctrl button.

Below code would do the job for us. It will take you to the last un-break cell in the selected column.

Code:

Sub Range_Example4()

Range("A1").End(xlDown).Select

End Sub

Selecting Range with End Property – Example #5

Similarly, if you want to select the last column need to use xlRight method instead of xlDown method.

Code:

Sub Range_Example5()

Range("A1").End(xlToRight).Select

End Sub

Selecting Full Table with Range with End Property – Example #6

Assume you have a data as shown in the below image.

If you want to select the data you will simply select it from A1:C6. Similarly, in VBA we can supply the code as Range (“A1:C6”).Select. This is simple, but if your data keeps changing how will you select.

By using below code we can select the entire range even if the range changes at any given point of time.

Code:

Sub Range_Example6()

Range("A1", Range("A1").End(xlToRight).End(xlDown)).Select

End Sub

Run this code using F5 key or manually to see results.

Now increase the data horizontally and vertically. I have increased the data which is highlighted.

Now again run the code using F5 key or manually and still, it would do the job for me.

Insert Values to Cells with Range Property – Example #7

After all these you must be thinking how do we insert values to cells? Yes, we can insert values to a cell. Below is the simple code which can insert values to the cells.

Code:

Sub Range_Insert_Values()

Range("A1").Value = 20
Range("A2").Value = 80

End Sub

Now run the code using F5 key or manually then the code will insert value 20 to cell A1 & insert value 80 to cell A2.

Cells vs Range

I have shown two different methods of referring to the cell one is using CELLS property & another one is using RANGE property. Even though both look similar there are differences to it.

• Cells can select only one cell at a time i.e. Cells (1, 1).Select means in the first-row first column select i.e. A1 cell. If you want to select A1 & A2 together CELLS property cannot do this task.
• Range can select many cells at a time i.e. Range (“A1:A5”).Select means this will select the cells from A1 to A5.

Things to Remember

• We can perform all those methods related to RANGE property in VBA as well.
• CELLS property can select only one cell but RANGE property can select many cells at a time.
• If you want to insert text values to the cells you need to enclose the text with double quotes.

Recommended Articles

This has been a guide to VBA Range. Here we discussed how to use Range object along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. How to Use VBA Function in Excel?
2. Guide to Range in Excel
3. How to use VBA VLOOKUP Function?
4. Guide to VBA TRIM Function