End of a Range Using VBA in Excel (With Examples)

Get FREE Advanced Excel Exercises with Solutions!

If you are working with Microsoft Excel’s VBA codes, you may know how important this is. Excel’s VBA code saves a lot of time. It can help you analyze a large amount of data quickly. Now, the End function of VBA selects the end of a range at ease. In this tutorial, you will learn how to use Range.End method using VBA in Excel with suitable examples and proper illustrations. Look at the below overview before diving into the examples in the next section.

Range End VBA


Download Practice Workbook

Download this practice workbook.


Range.End Property in Excel VBA

The Range.End allows a user moves to a cell in a particular direction in the current working region. It returns a Range object. Also, it represents the cell at the end of the range that contains the source range. It is equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only Range object.

Syntax:

expression.End(direction)

Here, expression is a range object.

Argument:

Name Requirement  Data Type Description
Direction Required xlDirection  The direction to move

Select Last Entry of a Row Using Range.End in Excel

Now, the Range.End method selects the last non-empty cell in a given range. It’s like pressing Ctrl+Right Arrow on your keyboard. You can either go to the left or right.

The Syntax will be like the following:

Range(“A1”).End(xlToRight) or Range(“A5”).End(xlToLeft)

Now, this code means it will start from cell A1 or A5, and after that, it will move to the last non-empty cell.

Note
If your selected range doesn’t have values, it will move to the last cell of that range.

Take a look at this screenshot:

dataset of range end vba in excel

Here, we have some values. Now, we select the last entry of this row using Range.End.

📌 Steps

First, press Ctrl+F11 on your keyboard to open the VBA editor. Then, click on Insert > Module.

insert VBA module in excel

Then, type the following code:

Sub move_to_right()
Range("C4").End(xlToRight).Select
End Sub

Save the file and run the code.

cell moved to right in range end vba in excel

As you can see, we have selected the last entry of the row using Range.End in VBA.

Similarly, to move from right to the left, type the following code:

Sub move_to_left() 
Range("G4").End(xlToLeft).Select 
End Sub

range end vba in excel


Select Last Entry of a Column Using VBA Range.End

Similar to the previous method. You can also select the last entry of a column. Just you have brought a slight change in your VBA code.

The Syntax will be like the following:

Range(“A1”).End(xlDown) or Range(“A5”).End(xlUp)

Now, this code means it will start from cell A1 or A5, and after that, it will move to the last non-empty cell of that particular column.

Note
If your selected range doesn’t have values, it will move to the last cell of that range.

Take a look at this screenshot:

dataset of range end vba in excel

Here, we have some values. Now, we select the last entry of this column using Range.End.

📌 Steps

First, press Ctrl+F11 on your keyboard to open the VBA editor. Then, click on Insert > Module.

insert VBA module in excel

Then, type the following code:

Sub move_down()
Range("B5").End(xlDown).Select
End Sub

Save the file and run the code.

cell moved down in range end vba of excel

As you can see, we have selected the last entry of the column using Range.End in VBA.

Similarly, to move from down to up, type the following code:

Sub move_up()
Range("B9").End(xlUp).Select
End Sub

output of the method

All these VBA codes are almost the same. Just slight changes will give you desired results.


Similar Readings:


Select an Entire Range of Cells by Range.End in Excel

Selecting an entire range of cells in Excel is one of the significant tasks. We perform a lot of operations by selecting a whole range of cells. So, the meaningfulness of this method is enormous.

Take a look at this screenshot:

Select an Entire Range of Cells by Range.End in Excel

Here, we have some sample data. Now, if we select the range of cells B5:G9, it will look like the following screenshot:

choose any of the option to select the entire range of cells

We can perform it in two ways. Either use the mouse or press Ctrl+Shift+Right Arrow+ Down Arrow on your keyboard. But, if your dataset is large, you may face some problems while selecting that.

So, you can use the VBA codes to select a large amount of data at ease. This simple code will reduce your stress definitely.

📌 Steps

First, press Ctrl+F11 on your keyboard to open the VBA editor. Then, click on Insert > Module.

insert VBA module in excel

Then, type the following code:

Sub select_entire_range()
Range(Range("B5"), Range("B5").End(xlToRight).End(xlDown)).Select
End Sub

Range(Range(“B5”), Range(“B5”).End(xlToRight).End(xlDown)).Select 

This line of code first selects cell B5. Then, it will move to the right of that row using Range(“B5”).End(xlToRight).

From there, it will move to last entry of that column using  Range(“B5”).End(xlToRight).End(xlDown).

It will pass the address of those first and last cells to another Range function. With the Select option, it will select the entire range of cells.

Save the file and run the code.

entire range of cells are selected using range end vba in excel

As you can see, we have selected the entire range of cells using Range.End in Excel.


An Example: VBA Code to Find Last Non-empty Row & Column Using Range.End in Excel

Now, in this section, we will show an example of Range.Find method using VBA in Excel.

To demonstrate this, we are going to use the previous dataset:

datset of excel range end vba

We are going to find the last non-empty row and column from the dataset using Range.End.

📌 Steps

First, press Ctrl+F11 on your keyboard to open the VBA editor. Then, click on Insert > Module.

Then, type the following code:

Sub last_used()

Dim last_row As Integer
Dim last_column As Integer

Range("B5").Select

last_row = Range("B5").End(xlDown).Row
last_column = Range("B5").End(xlToRight).Column

MsgBox "Last Used Row: " & last_row & vbCrLf & "Last Used Column: " & last_column

End Sub

Range(“B5”).End(xlDown).Row: It will start from cell B5 and move to the last used row. After that, it will return the row number.

Range(“B5”).End(xlToRight).Column:  It will start from cell B5 and move to the last used column. After that, it will return the column.

Save the file. Then press Alt+F8 on your keyboard to open the macro dialog box. Then select last_used. Click on Run.

Final output of the code

After that, you will the last used row and column. So, we are successful in using the Range.End method in Excel.


💬 Things to Remember

The Range.End method moves to the last non-empty cell. So, if your dataset has blanks in between them, it will give you a different result.

It only works for a single row or column. In order to select a range of cells, you have to combine them as we showed earlier.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about Range.End using VBA in  Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo