EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
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?
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • 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

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

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

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, 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

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

Forgot Password?

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