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 Selection Range
 

VBA Selection Range

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Selection Range

Excel VBA Selection Range

We all might have seen the process where we need to select the range so that we could perform some work on it. This is the basic step towards any task we do in Excel. If we do any manual work, then we can select the range of cells manually. But, while automating any process or work it is necessary to automate the process of Selection of Range as well. And VBA Selection Range is the basic steps toward any VBA code. When we write the steps for Selection of Range, their Range becomes the Object and Selection becomes the property. Which means the cells which we want to select are Objects and selection process of the property in VBA Selection Range.

 

 

How to Select a Range in Excel VBA?

We will learn how to select a range in Excel by using the VBA Code.

Watch our Demo Courses and Videos

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

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

Excel VBA Selection Range – Example #1

In the first example, we will see a very simple process where we will be selecting any range of cells using VBA code. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab where we will be writing the code for this.

Insert a Module

Step 2: Write the subcategory of VBA Selection Range or we can choose any other name as per our choice to define it.

Code:

Sub Selection_Range1()

End Sub

VBA Selection Range Example 1-2

Step 3: Now suppose, we want to select the cells from A1 to C3, which forms a matrix box. Then we will write Range and in the brackets, we will put the cells which we want to select.

Code:

Sub Selection_Range1()

Range("A1:C3")

End Sub

Range Example 1-3

Step 4: Now we have covered the cells. Further, we can apply any function to it. We can select the cells, select the values it has or copy the selected range as well. Here we will simply select the range.

Code:

Sub Selection_Range1()

Range("A1:C3").Select

End Sub

VBA Selection Range Example 1-4

Step 5: Now compile the code and run it by clicking on the Play button located below the menu bar. We will see the changes in the current sheet as cells from A1 to C3 are selected as shown below.

VBA Selection Range Example 1-5

In a similar way, we can put any value to selected range cells. For this we will use Value function instead of Select.

Code:

Sub Selection_Range2()

Range("A1:C3").Value

End Sub

VBA Selection Range Example 1-6

Now assign any value or text which we want to see in the selected range cells. Here that value is My Range.

Code:

Sub Selection_Range2()

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

End Sub

VBA Selection Range Example 1-7

Now again run the code by clicking on the Play Button.

Result of Example 1-8

We will see the required text which we were in code value is got printed to the selected range.

Excel VBA Selection Range – Example #2

There is another way to implement VBA Selection Range. For this, follow the below steps:

Step 1: Write the subcategory of VBA Selection Range as shown below.

Code:

Sub Selection_Range3()

End Sub

VBA Selection Range Example 2-1

Step 2: By this process, we can select the range of any specific sheet which we want. We don’t need to make that sheet as current. Use Worksheet function to activate the sheet which wants by putting the name or sequence of the worksheet.

Code:

Sub Selection_Range3()

Worksheets("Sheet2").Activate

End Sub

Use Worksheet function

Step 3: Now again as per example-1, we will select the range of the cells which want to select. Here we are considering the same range from cell A1 to C3.

Code:

Sub Selection_Range3()

Worksheets("Sheet2").Activate
Range("A1:C3").Select

End Sub

VBA Selection Range Example 2-3
Step 4: Now run the code by clicking on the Play Button. We will see, the cells from A1 to C3 of the worksheet which is Name as Sheet2 are now selected.

VBA Selection Range Example 2-4

As we have already selected the cells which we wanted, so now we can again write the one line code by which we will insert any text to selected cells. Or we can select the new range of cells manually also to see the changes by this code.

Step 5: For this use Selection function along with Value and choose the value which we want to see. Here our value is the same as we used before as My Range.

Code:

Sub Selection_Range3()

Worksheets("Sheet2").Activate
Range("A1:C3").Select
Selection.Value = "My Range"

End Sub

Use Selection function

Step 6: Now again run the code by clicking on Play Button.

VBA Selection Range Example 2-6

We will see, the selected cells from A1 to C3 got the value as My Range and those cells are still selected.

Excel VBA Selection Range – Example #3

In this example, we will see how to move the cursor from a current cell to the far most end cell. This process of selecting the end cell of the table or blank worksheet is quite useful in changing the location from where we can select the range. In Excel, this process is done manually by Ctrl + any Arrow key. Follow the below steps to use VBA Selection Range.

Step 1: Write the subcategory of VBA Selection Range again.

Code:

Sub Selection_Range4()

End Sub

VBA Selection Range Example 3-1

Step 2: Choose the reference range cell from where we want to move the cursor. Let’s say that cell is B1.

Code:

Sub Selection_Range4()

Range("B1")

End Sub

VBA Selection Range Example 3-2

Step 3: Now to move to the End table or sheet towards right we will use xlToRight and for left it would be changed to xlToLeft as shown below.

Code:

Sub Selection_Range4()

Range("B1").End(xlToRight).Select

End Sub

VBA Selection Range Example 3-3

Step 4: Now run the code by pressing F5 key.

VBA Selection Range Example 3-4

We will see, our cursor from anywhere from the first row or cell B1 will move to the far end to the sheet.

In a similar way, we can move the cursor and select the cell of the far down or up location of any sheet by xlDown or xlUP. Below is the code for selecting the far down cell of a sheet from reference cell B1.

Code:

Sub Selection_Range4()

Range("B1").End(xlDown).Select

End Sub

VBA Selection Range Example 3-5

Pros of Excel VBA Selection Range

  • This is as easy as selecting the range of cells manually in Excel.
  • We can choose any type of range which we cannot do manually.
  • We can select and fill the cells which are only possible in excel by Find and Replace option.
  • Selecting the range cells and putting the data into that can be done simultaneously with one line of code.

Things to Remember

  • Using xlDown/Up and xlToLeft/Right command in code will take us to cells which is a far end or to the cell which has data. Means, the cell with the data will stop and prevent us from taking to the far end of the sheet.
  • We can choose any type of range but, make sure the range of cells is in sequence.
  • Random selection of cell is not allowed with these shown examples.
  • Always save the excel file as Macro Enable excel to prevent losing the code.

Recommended Articles

This is a guide to VBA Selection Range. Here we discuss how to select a range in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Conditional Formatting
  2. VBA Remove Duplicates
  3. Excel Named Range
  4. VBA XLUP

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 Selection Range Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW