EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Range Cells
 

VBA Range Cells

Madhuri Thakur
Article byMadhuri Thakur

Updated June 9, 2023

VBA Range Cells

 

 

VBA Range Cells

In VBA, the range is also called the property of an individual cell or a group of cells in any row or a column. The range is particularly a data set in Excel. For example, cell A1 to cell B3 is a range of cells if they contain a value of some sort or they are empty.

Watch our Demo Courses and Videos

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

In VBA, we make codes to automate our tasks. To automate our tasks, we refer to the value in Excel cells. Those sets of individual or multiple cells are called range in Excel. We can specify which rows or column range we refer to in VBA.

Syntax of Range in Excel VBA

VBA Range has the following syntax:

Syntax of Range

The syntax to use range expression in VBA is as follows:

Expression.Range(“Cell Range”)

To use the range function, we need to understand three basic things in VBA –

  • Object Qualifier: This is the workbook or the worksheet we refer to.
  • Property: This is the function to use the properties of the object.
  • Method: This function is what we want to do with the data we selected.

For example, Application.Workbooks(“ANAND.xlsm”).Worksheets(“Sheet1”).Range(“A1”) The object qualifier is Application. Workbooks in which we referred to ANAND named Excel file and Sheet 1 as a worksheet; if we have to change the font, that would be our method. This will be clear with more examples.

Note: To use VBA, ensure the developer’s tab is enabled from File Tab from the options section.

How to Use Excel VBA Range Cells?

We will learn how to use VBA Range Cells with a few examples in Excel.

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

VBA Range Cells – Example #1

Let us first change the font of our cell A2, which is the Value of “ANAND,” to bold using the VBA range function.

 VBA Range Cells Example 1-1

Follow the below steps to use VBA Range Function.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

VBA Range Cells Example 1-2

Step 2: Click on Insert Tab and go to the module to open the code window.

VBA Range Cells Example 1-3

Step 3: Declare a sub-function in the code window. Cell A2 has in sheet 1.

Code:

Sub Sample()

End Sub

VBA Range Cells Example 1-4

Step 4: In the code, activate the worksheet by the following code.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

VBA Range Cells Example 1-5

Step 5: Now, we will change the font for the specific cell using the range function in VBA.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

Range("A2").Font.Bold = True

End Sub

VBA Range Cells Example 1-6

Step 6: Run the above code from the run button provided or press f5.

Result of Example 1-7

Once we have run the code, we can see the following result.

VBA Range Cells – Example #2

Now let us change the colors of the specific range of cells. For demonstration purposes, I have made cell range B2:C5 in red color. With the VBA range function, I want to select them and clear the font. Here is the original representation of those cells.

Follow the below steps to use Excel VBA Range Cells.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab to insert a new module to open the code window. Click on the module inserted to start writing the code.

Step 3: Declare a sub-function in the code window.

Code:

Sub Sample1()

End Sub

VBA Range Cells Example 2-2

Step 4: Activate the worksheet with the below function.

Code:

Sub Sample1()

Worksheets("Sheet1").Activate

End Sub

VBA Range Cells Example 2-3

Step 5: Now, we select the cells and clear the format using the range function.

Code:

Sub Sample1()

Worksheets("Sheet1").Activate

Range("B2:C5").ClearFormats

End Sub

VBA Range Cells Example 2-4

Step 6: Run the code, and see the result from the run button provided.

Result of Example 2-5

VBA Range Cells – Example #3

Let us merge a few cells using the VBA range function. For demonstration purposes, Let us see what the current cells look like without merging.

Result of Example 3-1

Follow the below steps to use Excel VBA Range Cells.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab to insert a new module to open the code window. Click on the module inserted to start writing the code.

Step 3: Declare a sub-function in the code window.

Code:

Sub Sample2()

End Sub

VBA Range Cells Example 3-2

Step 4: Activate the workbook with the below function.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

End Sub

VBA Range Cells Example 3-3

Step 5: Now, we use the VBA Range function for the merge cells as follows.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

Range("A6:D6").Merge

End Sub

VBA Range Cells Example 3-4

Step 6: Run the code by the run button or press f5 to see the result.

Result of Example 3-5

VBA Range Cells – Example #4

Now let us select a range of cells and change their color format. In example one, we selected only one cell. But in this example, we will select a bunch of cells of blue color. For demonstration purposes, I have data in cell range E2:F4 as follows:

VBA Range Cells Example 4-1

Follow the below steps to use VBA Range Cells.

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab to insert a new module to open the code window. Click on the module inserted to start writing the code.

Step 3: Declare a sub-function in the code window.

Code:

Sub Sample3()

End Sub

VBA Range Cells Example 4-2

Step 4: Activate the worksheet with the following code below,

Code:

Sub Sample3()

Worksheets("Sheet1").Activate

End Sub

VBA Range Cells Example 4-3

Step 5: Now, let us change the color of cell range E2:F4 using the VBA range function.

Code:

Sub Sample3()

Worksheets("Sheet1").Activate

Range("E2:F4").Interior.ColorIndex = 37

End Sub

VBA Range Cells Example 4-4

Step 6: Run the code by pressing F5 or from the run button provided and see the result.

Result of Example 4-5

Things to Remember

  • The range can be one cell or multiple cells.
  • If we are referring to any object, we use the dot operator(.), such as range(“A1”). Select is used to select cell A1.
  • We need methods and properties of a cell to use range values in Excel VBA.

Recommended Articles

This has been a guide to VBA Range Cells. Here we have discussed how to use Excel VBA Range Cells, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VBA Replace
  2. Excel Named Range
  3. VBA Number Format
  4. VBA IsError

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA Range Cells Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW