WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Set Range

VBA Set Range

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Set Range in Excel VBA

Set range in vba means we specify a given range to the code or the procedure to execute, if we do  not provide a specific range to a code it will automatically assume the range from the worksheet which has the active cell so it is very important in the code to have range variable set.

After working with excel so many years, you must have understood the fact that all works we do are on the worksheet, and in worksheets, it is cells that contain the data. So when you want to play around with data, you must be a behavior pattern of cells in worksheets. So when the multiple cells are get together, it becomes a RANGE. To learn VBA, you should know each and everything about cells and ranges. So in this article, we will show you how to set the range of cells that can be used for VBA coding in detail.

VBA Set Range

What is the Range Object?

Range in VBA is referred to as an object. A range can contain a single cell, multiple cells, a row or column, etc.…

In VBA, we can classify the range as below.

“Application >>> Workbook >>> Worksheet >>> Range”

First, we need to access the Application. Then under this, we need to refer to which workbook we are referring to, and in the workbook, we are referring to which worksheet we are referring to, and then in the worksheet, we need to mention the range of cells.

Using the Range of cells, we can enter the value to the cell or cells, we can read or get values from the cell or cells, we can delete, we can format, and we can do many other things as well.

How to Access Range of Cells in Excel VBA?

You can download this VBA Set Range Excel Template here – VBA Set Range Excel Template

In VBA coding, we can refer to the cell by using VBA CELLS property and RANGE object. For example, if you want to refer to the cell A1 then first, we will see using RANGE object.

Inside the subprocedure, we need to first open the RANGE object.

Code:

Sub Range_Examples()

 Range(

End Sub

VBA Set Range Example 1

As you can see above, the RANGE object asking what is the cell we are referring to. So we need to enter the cell address in double-quotes.

Code:

Sub Range_Examples()

 Range ("A1")

End Sub

VBA Set Range Example 1-1

Once the cell address is supplied, we need then decide on what to do with this cell by using properties and methods. Now put a dot to see the properties and methods of the RANGE object.

VBA Set Range Example 1-2

If we want to insert the value to the cell, then we need to choose the “Value” property.

Code:

Sub Range_Examples()

 Range("A1").Value

End Sub

VBA Set Range Example 1-3

To set value, we need to put an equal sign and enter the value that we want to insert to cell A1.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Range_Examples()

 Range("A1").Value = "Excel VBA Class"

End Sub

VBA Set Range Example 1-4

Run the code through the run option and see the magic in cell A1.

VBA Set range Example 1.gif

As mentioned in the code, we have got the value in cell A1.

Similarly, we can refer to the cell by using CELLS property as well. Open CELLS property and see the syntax.

VBA Set Range Example 2

This is unlike the RANGE object, where we can enter the cell address directly in double-quotes. Rather, we need to give row number and column to refer to the cell. Since we are referring to the cell A1, we can say the row is 1, and the column is 1.

VBA Set Range Example 2-1

After mentioning the cell address, we can use properties and methods to work with cells. But the problem here is unlike range object after putting dot. We don’t get to see the IntelliSense list.

VBA Set Range Example 2-2

So, you need to be an expert to refer to the cells by using CELLS property.

Code:

Sub CELLS_Examples()

 Cells(1, 1).Value = "Excel VBA Class"

End Sub

VBA Set Range Example 2-3

Accessing Multiple Cells & Setting Range Reference in VBA

One of the big differences between CELLS & RANGE is using CELLS. We can access only one cell but using RANGE. We can access multiple cells, as well.

For example, for cells A1 to B5, if we want the value of 50, then we can write the code as below.

Code:

Sub Range_Examples()

 Range("A1:B5").Value = 50

End Sub

VBA Set Range Example 3

This will insert the value of 50 from cell A1 to B5.

Insert values Example 3-1

Instead of referring to the cells directly, we can use the variable to hold the reference of specified cells.

First, define the variable as the “Range” object.

Code:

Sub Range_Examples()  

  Dim Rng As Range

End Sub

Dim Example 2-4

Once the variable is defined as the “Range” object, we need to set the reference for this variable about what are the cell addresses are going to hold the reference to.

To set the reference, we need to use the “SET” keyword and enter the cell addresses by using the RANGE object.

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Range("A1:B5")

End Sub

set Example 3-2

Now the variable “Rng” refers to the cells A1 to B5.

Instead of writing the cell address Range (“A1:B5”), we can simply use the variable name “Rng.”

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Range("A1:B5")

  Rng.Value = "Range Setting"

End Sub

Value Example 3-3

Now this will insert the mentioned value from A1 cell to B5 cell.

VBA Set range Example 1.gif

Assume you want whatever the selected cell should be a reference, then we can set the reference as follows.

Code:

Sub Range_Examples()

  Dim Rng As Range

  Set Rng = Selection

  Rng.Value = "Range Setting"

End Sub

selection Example 3-4

This is a beauty because if I select any of the cells and run, it will insert the value to those cells as well.

For example, I will select certain cells.

select cells Example 3-5

Now I will execute the code and see what happens.

VBA Set Range Example 3-6

For all the selected cells, it has inserted the value.

Like this, we can set the range reference by declaring variables in VBA.

Things to Remember

  • The range can select multiple cells, but CELLS can select one cell at a time.
  • RANGE is an object, and CELLS is property.
  • Any object variable should be set the reference of the object by using the SET keyword.

Recommended Articles

This has been a guide to VBA Set Range. Here we discuss how to set the range of excel cells that can be used for reference through VBA code along with examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • VBA Set
  • VBA Sort Option
  • Range Cells in VBA
  • Using Range Objects in VBA
  • VBA INT
5 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Set Range Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More