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

By Ashwani JaiswalAshwani Jaiswal

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.

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 (85,982 ratings)

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