EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Range Cells
Secondary 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
    • VLOOKUP with Different Sheets

VBA Range Cells

By Madhuri ThakurMadhuri Thakur

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 sorts or they are empty.

In VBA, we make codes to automate our tasks. To automate our tasks, we refer to value in excel cells. Those set of cells, either individual or multiple, are called range in excel. In VBA, we can specify which rows or column range we are referring to.

Syntax of Range in Excel VBA

VBA Range has the following syntax:

Syntax of Range

Watch our Demo Courses and Videos

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

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

Expression.Range(“Cell Range”)

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

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,142 ratings)
  • Object Qualifier: This is basically the workbook or the worksheet we are trying to 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 worksheet if we have to change the font, then that would be our method. This will be clear with more examples.

Note: In order to use VBA, ensure to have the developer’s tab 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 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 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: Now, 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 purpose, 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 by 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 from the run, button provided see the result.

Result of Example 2-5

VBA Range Cells – Example #3

Let us merge few cells using the VBA range function. For demonstration purpose Let us see how the current cells look like without merge.

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, for the merge cells, we use the VBA Range function 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 the color format for them. In example one, we had selected only one cell. But in this example, we will select a bunch of cells to 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.
  • To use range value in excel VBA, we need methods and properties of a cell.

Recommended Articles

This has been a guide to VBA Range Cells. Here we have discussed how to use Excel VBA Range Cells along with 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
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • 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

ISO 10004:2018 & ISO 9001:2015 Certified

© 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 Login

Forgot Password?

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

*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.

Let’s Get Started

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