EDUCBA

Home VBA VBA Resources VBA Coding VBA END

VBA END

Madhuri Thakur
Article byMadhuri Thakur

Excel VBA End

VBA END Function

End Statement is almost used in every other programming language, so VBA is also not different from it. Every code has a start and has an end for it. How do we end any specific function or code is different in programming languages. In VBA we close our code by using END Statement. But apart from this end statement, we have another end function in VBA which is used to refer to the cells of a worksheet which we will talk about detail in this article.

 

 

As I have told above we will be discussing another property of END in VBA which is used to refer to the end of the cells. There are many separate properties for this  END function. For example, end to the right or end the left or end to the bottom. To make this more clear look at below the image.

Watch our Demo Courses and Videos

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

VBA End Example 1.1

In Excel worksheet how do we move from cell A1 which points A to cell E1 which is point B? We press CTRL + right arrow. Similarly to move from point B to point C we press CTRL + Down Arrow and from point C to point D we press CTRL + Left arrow. Similarly for point D to point A we press CTRL + Up arrow.

This is also known as to refer to the next cell which has some value in it. This process skips the blank cells and moves to the end of the reference. In VBA we do not press CTRL + Right Arrow to move from point A to point B. We use properties of END to do this. And this is what we will learn in this article. How can we move from Point A to End to the right which is point B and select the cell range and do the same for others.

How to Use VBA End Function in Excel?

We will learn how to use a VBA END Function with example in excel.

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

Let us learn to do so by a few examples.

Example #1 – VBA END

In the first example let us select cell E1 using the end property in VBA.

Step 1: From the Insert tab insert a new module. Remember we will work in the same module for the entire article. We can see the module in the project window, Open the module as shown below.

Module

Step 2: Start the Sub procedure in the window.

Code:

Sub sample()

End Sub

VBA End Example 1.2

Step 3: Now we know that we have to move from cell A1 to cell E1 so type the following code.

Code:

Sub sample()

Range ("A1")

End Sub

VBA End Example 1.3

Step 4: Now put a dot after the parenthesis and write end as shown below.

Code:

Sub sample()

Range("A1").End

End Sub

Example 1.4

Step 5: Press Enter and open a parenthesis we will see some more options in the end statement as follows,

Code:

Sub sample()

Range("A1").End(

End Sub

Example 1.5

Step 6: Select XltoRight as we have to move right to select cell E1.

Code:

Sub sample()

Range("A1").End (xlToRight)

End Sub

Example 1.6

Step 7: Now to select the range, put a dot after the closing parenthesis and write select as shown below.

Code:

Sub sample()

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

End Sub

VBA End Example 1.7

Step 8: Now let us execute the code written above and see the result in sheet 1 as follows.

VBA End 1

From Point A which is cell A1 we moved to the end of the data in the right which is cell E1.

Example #2 – VBA END

Similar to the above example where we moved right from the cell A1 we can move left too. Let us select cell A5 which is point C from Point D.

Step 1: In the same module, declare another subprocedure for another demonstration.

Code:

Sub Sample1()

End Sub

VBA End Example 2.1

Step 2: Now let us move from cell E5 to cell A5, so first refer to cell E5 as follows.

Code:

Sub Sample1()

Range ("E5")

End Sub

VBA End Example 2.2

Step 3: Now let us move to the left of the cell E5 using the end statement.

Code:

Sub Sample1()

Range("E5").End (xlToLeft)

End Sub

Example 2.3

Step 4: Now to select cell A5 put a dot after the parenthesis and write select.

Code:

Sub Sample1()

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

End Sub

Example 2.4

Step 5: Now execute this code above and see the result in sheet 1 as follows.

VBA End 3

From point C we moved to point D using the end statement.

Example #3 – VBA END

Now let use the downwards end statement which means we will select cell A5 from cell A1.

Step 1: In the same module, declare another subprocedure for another demonstration.

Code:

Sub Sample2()

End Sub

VBA End Example 3.1

Step 2: Now let us move from cell A5 to cell A1, so first refer to cell A1 as follows.

Code:

Sub Sample2()

Range ("A1")

End Sub

VBA End Example 3.2

Step 3: Now let us move to the down of the cell A1 using the end statement.

Code:

Sub Sample2()

Range("A1").End (xlDown)

End Sub

Example 3.3

Step 4: Now to select cell A5 put a dot after the parenthesis and write select.

Code:

Sub Sample2()

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

End Sub

Example 3.4

Step 5: Now execute this code above and see the result in sheet 1 as follows.

VBA End 4

We have moved from Point A to point D using the down property of the end statement.

Example #4 – VBA END

Now let us select the total range from Point A to Point B to Point C and to Point D using the end statement.

Step 1: In the same module, declare another subprocedure for another demonstration.

Code:

Sub FinalSample()

End Sub

VBA End Example 4.1

Step 2: Now let us select from cell A1 to cell E5, so first refer to cell A1 as follows.

Code:

Sub FinalSample()

Range("A1"

End Sub

VBA End Example 4.2

Step 3: Now let us move down of the cell A1 using the end statement.

Code:

Sub FinalSample()

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

End Sub

Example 4.3

Step 4: Now we need to move to the right of the cell A1 by using the following end statement as follows.

Code:

Sub FinalSample()

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

End Sub

Example 4.4

Step 5: Select the cell range using the select statement.

Code:

Sub FinalSample()

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

End Sub

VBA End Example 4.4

Step 6: Let us run the above code and see the final result in sheet 1 as follows.

VBA End

Things to Remember

  • The method to use END in VBA Excel to refer cells is very easy. We refer to a range first
  • Range( Cell ) and then we use End property to select or go to the last used cell in the left-right or down of the reference cell
  • Range (Cell).End(XltoRight) to got to the right of the cell.
  • First things which we need to remember is END property is different to the ending of a procedure or a function in VBA.
  • We can use a single property to refer to a cell i.e. to right or left of it or we can select the whole range together.
  • In a worksheet, we use the same reference using the CTRL button but in VBA we use the END statement.

Recommended Articles

This is a guide to VBA END. Here we discuss how to use Excel VBA END Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA InStr
  2. VBA Integer
  3. VBA ISNULL
  4. VBA Transpose