WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA Range Cells

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

Excel VBA Range Cells

In VBA when we refer to any data whether it be any cells or selection we use range property of VBA, using range property we can access any part of the worksheet and it is an inbuilt property, range cells means using the range property for a single cell like range.(“A1”) here we have referred to cell A1.

As a learner of VBA, it is very important to understand the concept of the range of cells to do any activity in the spreadsheet. In this article, we will explain to you the important techniques of Range Cells.

  • You can call it as CELLS, or you can call it as RANGE. There is no difference in this. In VBA, we can reference the cells in two ways, one is using CELLS property, and another one is using the RANGE object.
  • Here it is important to understand the words PROPERTY and OBJECT. It is always a confusing thing at the start.
  • Using the CELLS property, we can reference one cell at a time, but using the RANGE object, we can reference multiple cells at a time in the same worksheet. Since RANGE is an object, we can set the range as a particular range of cells by using the word “Set,” we will see more examples in some time now.

VBA Range Cells

Syntax of RANGE object

Now take a look at the syntax of the VBA RANGE object.

VBA Range Formula

  • Cell1 is nothing but what is the first cell we are refereeing to. If we are referring to the cell A1, then the argument will be Range(“A1”). We can also refer to multiple cells here, for an example if we are referring from A1 to A5, then the code will be Range(“A1:A5”)
  • [Cell2] this is the second cell of the range we are referring to. This is an optional argument.

How to use Range Cells in VBA?

The moment we reference the cell by using the Range object, we can access all the properties and methods associated with it. Take a look at the below image.

Code:

Sub Range_Example1()

  Range("A1").

End Sub

VBA Range Cells Example 1

We can activate the cell. We can add a comment. We can copy, we can paste, etc.. Many things we can do with it.

For example, if we want to select the cell A1, we can use the Select method.

Code:

Sub Range_Example1()

  Range("A1").Select

End Sub

VBA Range Cells Example 1-1

This will select the cell A1 in the activate sheet.

VBA Range Cells Example 1-2

We can also select individual multiple cells as well. For example, if we want to select A1, B2, and C3 cells,s we can use the below code.

Code:

Sub Range_Example1()

  Range("A1,B2,C3").Select

End Sub

This will select the cells like this.

VBA Range Cells Example 1-3

We can also select a range of cells using the range object. For example, if we want to select the cells from A1 to B5, we can use the below code.

Code:

Sub Range_Example1()

  Range("A1:B5").Select

End Sub

This will select the range of cells from A1 to B5.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example 1-4

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

Example #1 – Inserting Values to Cells

Like how we have selected cells by using the RANGE object similarly, we can insert values as well.

For example, if we want to insert the word “Hello” to the cell A1, we can first reference the cell and use VBA VALUE property like the below one.

Example 2

Now to insert a value put an equal sign and enter the value in double-quotes.

Code:

Sub Range_Example2()

  Range("A1").Value = "Hello"

End Sub

This will insert the value “Hello” to the cell A1.

Example 2-1

We can also insert the same value to multiple cells as well. For this, we need to first mention what those cells are. The below code is the example of inserting the word “Hello” to the cells A1, B2, and C3.

Code:

Sub Range_Example2()

  Range("A1,B2,C3").Value = "Hello"

End Sub

This will insert the value “Hello” to the cells “A1, B2, and C3”.

Example 2-2

Example #2 – Reference cells from Another Sheet

Not only in the active worksheet, but we can also select the cells from another worksheet as well. For example, if we want to select the cells A1 to A5 from the worksheet “City List,” we can use the below code.

Code:

Sub Range_Example3()

  Worksheets("City List").Range("A1:A5").Select

End Sub

The first thing we need to do is we need to mention which worksheet we are referring to but using WORKSHEET object, i.e., Worksheets(“City List”).

Then we have used our RANGE object to reference the cells. Here I have used the SELECT method. You can use any other method associated with range objects in VBA.

Example #3 – Reference Cells from another Workbook

We can reference the range of cells from another workbook as well, like how we have mentioned the worksheet name above; similarly, we need to mention the workbook name along with its file extension.

For example, if you want to select the cells A1 to A5 from the worksheet “Sales Sheet” from the workbook “Sales File 2018,” then you can use the below code.

Code:

Sub Range_Example4()

  Workbook("Sales File 2018.xlsx").Worksheets("Sales Sheet").Range("A1:A5").Select

End Sub

Firstly we need to reference the workbook by using WorkbooK property. Workbook(“Sales File 2018.xlsx”).

Then, as usual, we have selected the worksheet Worksheet (“Sales Sheet”).

Then in that worksheet, range of cells selected, i.e., Range(“A1: A5”). Select

Example #4 – Use the word “SET” for Range Object

As I told at the start, we can reference the range object by using the word “set.” Now take a look at the below code.

Code:

Sub Range_Example5()

  Dim Rng As Range
  Set Rng = Worksheets("Sales Sheet").Range("A1:A5")

  Rng.Value = "Hello"

End Sub

The first thing we have done here is we have declared the variable “Rng” as RANGE.

Then we have used the word “Set” to set the range.

Worksheets(“Sales Sheet”).Range(“A1:A5”)

Now the variable “Rng” is equal to the range A1 to A5 in the worksheet “Sales Sheet.”

Since we have already set the range of cells for the variable “Rng,” now we can access all the properties and methods associated with it.  Rng. Value = “Hello” means in cells A1 to A5 value should be “Hello.”

Recommended Articles

This has been a guide to VBA Range Cells. Here we learn how to work with cells and ranges object in VBA along with practical examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • VBA IIF
  • Excel VBA Dictionary
  • Excel VBA Tutorial for Beginners
  • Select Cell in VBA
9 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ 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 Range Cells Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More