WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » VBA Range

VBA Range

Excel VBA Range Object

Range is a property in VBA is similar to the worksheet property, range property also has many applications and uses, when we write our code and specify a particular cell range or a particular cell it is done by the range property method, it is used to give reference to cells rows and columns.

As you Know, VBA is used to record & run macros and automate the excel tasks, and do repetitive tasks faster & accurately.

In the context of the excel worksheet, the VBA range object denotes cells, either single or multiple. The range object can include a single cell, an entire row or column, or multiple cells spread across rows & columns.

In order for VBA to run the macros & do the tasks, it needs to identify the cells on which the called tasks need to be performed. It is here, the concept of Range Objects finds its usefulness.

excel VBA Range

How to use the Range Object?

To refer to the objects in VBA, we use the hierarchical technique. There is 3 hierarchy:

  • Object Qualifier: It refers to the location of the object, like where it is, i.e., the workbook or worksheet being referred to.
  • The other 2 are used to do the manipulation of cell values. These are property & methods.
  • Property: Here, the information about the object is stored.
  • Method: It refers to the action that the object will perform.

For example, for Range, the method will be actions like sorting, formatting, selecting, clearing, etc.

This is the structure that is being followed whenever a VBA object is referred. These 3 are separated by a dot (.)

Application.Workbooks.Worksheets.Range

syntax

Range Syntax

Application.Workbooks(“Booknew.xlsm”).Worksheets(“Sheet3”).Range(“B1”)

Examples

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

Example #1 –  Referring to a Single Cell

Let’s suppose we need to select the cell “B2” in the “sheet1” in the workbook.

Follow the below steps:

  1. Open the excel. Please open one with the excel extension “.xlsm,” which means “Excel Macro-enabled Workbook.” “.xlsx” types excel workbook won’t allow you to save the macros you will be writing now.
  2. Now, once you have opened the workbook, you need to go to the VBA Editor. You can use the shortcut, “ALT+F11” to open the editor, or use the method below as shown in the screenshot:

VBA Range

You will see a screen similar to below:

VBA Range

Now, write the code, as shown in the below screenshot.

Public  Sub SingleCellRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“B2”).Select
End Sub

See in the excel screenshot below that currently, cell A2 is activated. After you run the code, note where the activated cell is.

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

VBA Range 1

Run the code like shown in the below screenshot:

Tip: You can also use the excel shortcut key, i.e., F5, to run the code

VBA Range 2

You will see cell “B2” is selected after the execution of the program.

VBA Range 3

What you are doing here is you are giving instructions to the program to go to a particular cell in a particular worksheet of a particular workbook and do the action as told, which is here to select.

Similarly, you can use the syntax to do the selection of a wide variety of cells & ranges and also do a different kind of actions on them.

Example #2 – Selecting an Entire Row

For example, here to select the second row. Run the below-given code to select an entire row

Public  Sub EntireRowRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“2:2”).Select
End Sub

VBA Range 4

Here the range(“2:2”) signifies the second row. You can return to your Excel worksheet and see the results, as shown in the screenshot below.

VBA Range 5

Example #3 – Selecting an Entire Column

For example, here to select the entire column C. Run the below-given code and see the results.

Public  Sub EntireRowRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“2:2”).Select
End Sub

After entering the above-given code, you will see the entire column has been selected in your excel worksheet. Refer to the screenshot below.

VBA Range 6

Here, range(“C:C”) signifies the Column C.

VBA Range 7

Similarly, you can select continuous cells, or non-contiguous cells, an intersection of cell ranges, etc.

Just make the below changes to the Range part shown in the code.

Example #4 – Selecting Contiguous Cells: Range(“B2:D6”)

VBA Range 8

Example #5 – Selecting Non-contiguous Cells: Range(“B1:C5, G1:G3”)

visual basic appplication 9

Example #6 – Selecting Range Intersection: Range(“B1:G5 G1:G3”)

[Note the absence of comma here]. Here you will see G1 to G3 getting selected, which are the common cells in the provided range.

visual basic appplication 10

Now, the next example will be to select a group of cells in the worksheet and merge them into one cell.

Example #7 – Merge a Range of Cells

Suppose you want to merge cells “B1:C5” into one. See the below-given code and follow along.

visual basic appplication 11

Here “.merge” is the action we are performing on the group of cells given in a range.

Example #8 – Clearing the Formatting on Range of Cells

Suppose cells “F2:H6” are highlighted in yellow, and we want to clear that excel formatting. Another scenario maybe, you want to remove all the formatting either in the entire worksheet or from a group of cells.

See the screenshots below to follow along. First, I will show you the formatted cells (F2: H6).

visual basic appplication 12

Please run the codes shown in the screenshot below to remove this formatting in the selected range of cells.

Syntax: ThisWorkbook.Worksheets(“Sheet1”).Range(“F2:H6”).ClearFormats

Public  Sub ClearFormats()
ThisWorkbook.Worksheets(“Sheet1”).Range(“F2:H6”).ClearFormats
End Sub

You can refer to this screenshot given below:

visual basic appplication 13

Similarly, you can clear the contents of a range of cells by using the action “.ClearContents.”

There are many such things you can do. Please try them to learn better.

Things to Remember

  • The range object denotes a single cell or multiple cells.
  • To manipulate cell values, we need to use the properties and methods.
  • To refer to objects in excel, Range follows the object hierarchy pattern using the “.” Notation.

Recommended Articles

This has been a guide to VBA Range. Here we learn how to select a particular cell and range of cells with the help of VBA range objects and examples in excel. You may also have a look at other articles related to Excel VBA –

  • Excel VBA Cells Reference
  • Use VLookup in VBA
  • Using VBA Msg Box
0 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 Excel Template

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