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

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.


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:

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

Tags:

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo