WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA End

VBA End

End Function in VBA

End is a statement in VBA which has multiple forms in VBA applications, simple End statement can be put anywhere in the code and it will automatically stop the execution of the code, end statement is used in many procedures like to end the subprocedure or to end any loop function like End if.

For everything, there is an end, and in VBA, it is no different. You must have seen this word “End” in all the codes in your VBA.  We can End in “End Sub,” “End Function,” “End If.” These are common as we know each End suggests the end of the procedure. These VBA End statements don’t require any special introduction because we are familiar with it in our VBA coding.

Apart from the above “End,” we have one property, “End” in VBA. In this article, we will take you through that property and how to use it in our coding.

VBA End Function

End Property in VBA

“End” is the property we use in VBA to move in the suggested direction. The typical example of direction is moving from the active cell to the last used cell or last entry cell horizontally and vertically in the worksheet.

For example, let’s recall this with a worksheet. Look at the below image.

VBA End Example 1

Right now, we are in the A1 cell.

If we want to move to the last used cell horizontally, we use the excel shortcut key Ctrl + Right Arrow, and it will take us to the last used cell horizontally.

VBA End Example 1-1

Similarly, if we want to move to the last used cell downwards or vertically, we press the shortcut key Ctrl + Down Arrow.

VBA End Example 1-2

So to move from left to right, we press Ctrl + Left Arrow. To move from bottom to top, we press Ctrl + Up Arrow.

A similar thing can be done in VBA but not by using the Ctrl key. Rather we need to use the word “End.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Examples of Excel VBA End Function

You can download this VBA End Excel Template here – VBA End Excel Template

Example #1 – Use VBA End Property To Move in Worksheet

Let’s look at how to use Excel VBA End to move in the sheet. First, we need to decide which cell we need to move. Ok, let’s say we need to move from the cell A1, so refer the cell by using the VBA Range object.

Code:

Sub End_Example1()

  Range ("A1")

End Sub

VBA End Example 2

Put dot (.) to see the IntelliSense list. Select “End” VBA property from the list.

Code:

Sub End_Example1()

  Range("A1").End

End Sub

VBA End Example 2-1

Once the end property selected open parenthesis.

Code:

Sub End_Example1()

  Range("A1").End(

End Sub

VBA End Example 2-2

As soon as you open parenthesis, we can see all the available options with “End” property. Select “xlToRight” to move from cell A1 to the last used cell horizontally.

Code:

Sub End_Example1()

  Range("A1").End (xlToRight)

End Sub

VBA End Example 2-3

After moving to the last cell, we need to select what we need to do. Put dot (.) to see the IntelliSense list.

Code:

Sub End_Example1()

  Range("A1").End(xlToRight).

End Sub

VBA End Example 2-4

Choose the “Select” method from the IntelliSense list.

Code:

Sub End_Example1()

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

End Sub

VBA End Example 2-5

This will make use of cell A1 to last used cells horizontally.

VBA End Example 2-6

Similarly, use the other three options to move right, left, down, up.

To Move Right from cell A1.

Code:

Sub End_Example1()

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

End Sub

To Move Down from cell A1.

Code:

Sub End_Example1()

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

End Sub

To Move Up from cell A5.

Code:

Sub End_Example1()

  Range("A5").End(xlUp).Select

End Sub

To Move Left from cell D1.

Code:

Sub End_Example1()

  Range("D1").End(xlToLeft).Select

End Sub

All the above codes are the sample examples of using the “End” property to move in the worksheet.

Now we will see how to select the ranges by using the “End” property.

Example #2 – Selection Using End Property

We need to End the property to select the range of cells in the worksheet. For this example, consider the below data.

VBA End Example 3

Select A1 to Last Used Cell

To select the cells from A1 to the last used cell horizontally, first, mention the cell A1 in Range object.

Code:

Sub End_Example2()

  Range("A1",

End Sub

VBA End Example 3-1

For the second argument, open one more Range object and mention the cell as A1 only.

Code:

Sub End_Example2()

  Range("A1",Range("A1")

End Sub

Example 3-2

Close only one bracket and put a dot to select the Excel VBA End property.

Code:

Sub End_Example2()

  Range("A1",Range("A1").End(

End Sub

Example 3-3

Now select xlToRight and close two brackets.

Code:

Sub End_Example2()

  Range("A1",Range("A1").End(xlToRight))

End Sub

Example 3-4

Now chose the “Select” method.

Code:

Sub End_Example2()

  Range("A1", Range("A1").End(xlToRight)).Select

End Sub

Example 3-5

Ok, we are done.

Run this code to see the impact.

Example 3-6

As you can see, it has selected the range A1 to D1.

Similarly, to select downwards, use the below code.

Code:

Sub End_Example2()

  Range("A1", Range("A1").End(xlDown)).Select
     'To select from left to right
End Sub

Code:

Sub End_Example2()

  Range("A1", Range("A1").End(xlDown)).Select
     'To select from top to down
End Sub

Code:

Sub End_Example2()

  Range("D1", Range("D1").End(xlToLeft)).Select
     'To select from right to left
End Sub

Code:

Sub End_Example2()

  Range("A5", Range("A5").End(xlUp)).Select
     'To select from bottom to up
End Sub

Example #3 – Select Right to Left, Right to Bottom, & Top

We have seen how to select horizontally & vertically. To select both vertically and horizontally, we need to use two “End” properties. To select the data from A1 to D5, we need to use the below code.

Code:

Sub End_Example3()

 Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
  'To from cell A1 to last use cell downwards & rightwards
End Sub

This will select the complete range like the below.

Example 4

Like this, we can use the VBA “End” Function property to select a range of cells.

Recommended Articles

This has been a guide to Excel VBA End Function. Here we learn how to use End Property in VBA and End Property Selection along with examples and a downloadable template. Below are some useful excel articles related to VBA –

  • VBA Split
  • Call Sub in Excel
  • Today in VBA
  • How to Paste in VBA?
9 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA End Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More