Updated April 8, 2023
Excel VBA Range Object
A range is nothing but a cell or series of cells. In VBA, we often refer to 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. This is because we usually do a lot of activities with a range of cells; for example, we copy, paste, edit, delete, etc. Similarly, in VBA, too, we have a specific set of codes 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 see 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. So, 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 the 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 the 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 select 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. As I have shown in the previous example, we can select the range of cells from A1 to A13 using the below method.
Code:
Sub Range_Example2() Worksheets("Sheet 1").Activate Range("A1", "A13").Select End Sub
Run this code using the F5 key or manually to see the result.
This would also perform the same task as the previous one. Similarly, if you select the range from different workbooks, you also need to specify the workbook name.
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 the Workbooks property, and I need to mention the full workbook name with its extension.
After selecting the Workbook, we need to select the worksheet using the 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 it in excel? Usually, you first select the cell A1, and you will hold the Ctrl key and press the down arrow key & it will select the last used cell.
Similarly, in VBA, the same logic will be applied, but you should use the END property instead of the regular excel Ctrl button.
The 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 the xlRight method instead of the 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 it.
By using the below code, we can select the entire range even if the range changes at any given point in time.
Code:
Sub Range_Example6() Range("A1", Range("A1").End(xlToRight).End(xlDown)).Select End Sub
Run this code using the 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 the 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:
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.
- The 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 the RANGE property in VBA as well.
- CELLS property can select only one cell, but the 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 the Range object along with some practical examples and a downloadable excel template. You can also go through our other suggested articles –
