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 Coding VBA Set Range
 

VBA Set Range

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 25, 2023

VBA Set Range

 

 

Excel VBA Set Range

To set the range in VBA, select the cell where we want to put the required content or move the cursor to the chosen cell. This helps us in building a code where we can select the partition we want. If we do not set the range, it will automatically choose the current cell where the cursor is placed. Excel VBA Set Range helps us choose the range per user requirements.

Watch our Demo Courses and Videos

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

In Excel, which is a common fact that whatever we do, contains a cell or table. Whether it has numbers or words, everything goes into the cell. It becomes very important for us to know the fact that choosing a cell where we want to place or the type of cell contact could be done easily. For that, we have the VBA Set Range, which helps us to put any value in any cell using the VBA code, where we can select the cell we want.

How to Use Set Range in VBA Excel?

We can choose a cell using a Range object and cell property in VBA. For example, if we want to choose cell A1 from any worksheet, we can use RANGE Object, as shown below.

As per the syntax of RANGE Object, it requires only Cell1, Cell2 as Range. If the number of cells exceeds 1, we can choose a colon instead of typing the cell name separated by commas (“:“). For example, if we want to select the cell from A1 to C5, we can use the format as RANGE(“A1:C5”) instead of typing each cell into the Range syntax brackets.

You can download this VBA Set Range Excel Template.xlsm here – VBA Set Range Excel Template.xlsm

Example #1

We will see the example where we will select the range to put anything we want. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: Write the subprocedure for VBA Set Range as shown below.

Code:

Sub VBA_SetRange()

End Sub

VBA Set Range Example 1-2

Step 3: Declare the variable using DIM as Range object as shown below.

Code:

Sub VBA_SetRange()

Dim MyRange As Range

End Sub

VBA Set Range Example 1-3

Step 4: Further setting up the range object with the declared variable MyRange, we will then choose the cell which want to include. Here those cells are A1 to B5.

Code:

Sub VBA_SetRange()

Dim MyRange As Range

Set MyRange = Range("A1:B5")

End Sub

Declared Variable Example 1-4

Step 5: Let’s consider a text which we want to insert in the selected range cells as TEST as shown below.

Code:

Sub VBA_SetRange()

Dim MyRange As Range

Set MyRange = Range("A1:B5")

MyRange.Value = "Test"

End Sub

VBA Set Range Example 1-5

Step 6: Once done, run the code after compiling. We will see the chosen cell range A1: B5 has now text as TEST as shown below.

VBA Set Range Example 1-6

Example #2

There is another way to apply VBA Set Range. In this example, we will see how to set Range in different cell range and choosing the different text into the 2 or more different cell Range. For this, follow the below steps:

Step 1: Open a module and directly write the subprocedure for VBA Set Range.

Code:

Sub VBA_SetRange2()

End Sub

VBA Set Range Example 2-1

Step 2: Open With-End With loop choosing the current worksheet as Sheet1.

Code:

Sub VBA_SetRange2()

With Worksheets("Sheet1")

End With

End Sub

With-End With loopA Example 2-2

Step 3: Let’s select the cell range choosing cell A1 and putting the value in the cell range A1 as TEST as shown below.

Code:

Sub VBA_SetRange2()

With Worksheets("Sheet1")

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

End With

End Sub

VBA Set Range Example 2-3

Step 4: Similar to the above-shown step, let’s choose another cell range from cell B2 to C4, choosing the value as MY RANGE as shown below.

Code:

Sub VBA_SetRange2()

With Worksheets("Sheet1")

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

.Range("B2:C4").Value = "My Range"

End With

End Sub

Sheet1 Example 2-4

Step 5: Now we can run the code if there is no error in compilation found. We would see both the cell of the selected range A1 and cells B2:C4 as shown below with chosen texts.

Test Example 2-5

Example #3

There is another simplest way to choose the VBA Set Range which is the simplest way. For this, follow the below steps:

Step 1: Again open the module and write the subprocedure, preferably in the name of VBA Set Range.

Code:

Sub VBA_SetRange3()

End Sub

VBA Set Range Example 3-1

Step 2: Choose the cell where you want to set the Range. We have chosen cell A1 as shown below.

Code:

Sub VBA_SetRange3()

Range("A1").Value

End Sub

VBA Set Range Example 3-2

Step 3: Put the name or value which we want to insert in the select Range cell. Here we are choosing MY RANGE again.

Code:

Sub VBA_SetRange3()

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

End Sub

VBA Set Range Example 3-3

Step 4: Now, if we run this code, we will see that cell A1 in the current worksheet will have the range value as MY RANGE as shown below.

VBA Set Range Example 3-4

Pros of VBA Set Range

  • It is very easy to implement, and also very important to know the way to set the Range in VBA.
  • We can choose and set more than 1 Range value.

Things to Remember

  • VBA Set Range is not limited to the examples which we have seen above. There are many ways to execute.
  • RANGE in VBA is an Object, and CELLS are the property that may contain anything.
  • We can use CELLS properties as well to set the Range in VBA.
  • If we use CELLS instead of RANGE, then we would only be able to set one cell, whereas, with the help of the RANGE object, we can choose any range or combination of cells.
  • It is always advised to save the Excel file in Macro and enable Excel format after writing the VBA Code to avoid losing the written code in the future.

Recommended Articles

We hope that this EDUCBA information on “VBA Set Range” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. How to Use VBA Login?
  2. VBA Month | Examples With Excel Template
  3. How to Use Create Object Function in VBA Excel?
  4. How to Use VBA IsError Function?

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 Set Range Excel Template.xlsm

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW