EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Range

By Jeevan A YJeevan A Y

Home » VBA » Blog » VBA Lookup & Reference Functions » VBA Range

VBA Range

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Range property includes cells as its parameter.

VBA Range Example 1-5

How to Use Excel VBA Range Object?

We will see how to use a VBA Range Object with few examples:

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

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

VBA Range Example 1-1

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

VBA Range Example 1-2

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.

Example 1-3

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

VBA Range Example 1-4

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.

VBA Range Example 2-1

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

VBA Range Example 3-1

Selecting Range with End Property – Example #4

Assume you have data as shown in the below image.

 VBA Range Example 4-1

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

Example 4-2

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

VBA Range Example 5-1

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

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

VBA Range Example 6-1

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

Example 6-2

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

Example 6-3

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

Example 6-4

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

Example 6-5

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

VBA Range Example 7-1

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.

Example 7-2

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 –

  1. How to Use VBA Function in Excel?
  2. Guide to Range in Excel
  3. Guide to VBA TRIM Function
  4. Excel VBA Selection Range | Examples

All in One Software Development Bundle (600+ Courses, 50+ projects)

600+ Online Courses

50+ projects

3000+ Hours

Verifiable Certificates

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Primary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) Learn More