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.
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:
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:
Now, this code means it will start from cell A1 or A5, and after that, it will move to the last non-empty cell.
Take a look at this screenshot:
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.
② Then, type the following code:
Sub move_to_right()
Range("C4").End(xlToRight).Select
End Sub
③ Save the file and run the code.
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
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:
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.
Take a look at this screenshot:
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.
② Then, type the following code:
Sub move_down()
Range("B5").End(xlDown).Select
End Sub
③ Save the file and run the code.
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
All these VBA codes are almost the same. Just slight changes will give you desired results.
Similar Readings:
- Excel Subscript Out of Range Error in VBA (with 5 Solutions)
- How to Use the Range Object of VBA in Excel (5 Properties)
- VBA for Each Cell in Range in Excel (3 Methods)
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:
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.
② 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.
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:
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.
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.