How To Use Excel Range Function End In VBA To Select The Entire Range Of Cells

The Excel Range object provides several properties. This article will tell you how to use the Range.End property to move the active cell in the specified direction to the first empty or non-empty cell.

1. Excel Range Function End Definition.

1. The Range.End(direction) function has a direction parameter.
2. The direction parameter’s value can be one of xlUp, xlDown, xlToLeft, xlToRight.
3. The Range.End(xlUp) will move the active cell from the current cell to the up direction until the first non-empty cell.
4. If there are no non-empty cells in that direction, then it will move to the first cell in that direction.
5. The Range.End property also returns an Excel Range object, you can call the range object’s Select property to select the range object.

2. How To Use The Excel Range End Property With Examples.

1. Now, we will run some examples to show you how to use the Range.End property and how it works.
2. Open an Excel workbook, and click the Developer tab —> Visual Basic icon to open the VBA window.
3. Click the View —> Immediate Window menu item to open the immediate window.
4. Add some data in your Excel worksheet Sheet1 like below.

                A            B            C            D            E            F            G            H            I
   1  
   2                       Jerry                    123456789   
   3  
   4                        Tom                     999999999   
   5

5. Input Sheets(“Sheet1”).Range(“D18”).End(xlUp).select in the immediate window and press the Enter key to run it, you can find it will selecting the cell D2. Because cell D2 is the first non-empty cell from D18 to the up direction.
6. Run the command Sheets(“Sheet1”).Range(“D2”).End(xlToLeft).select in the immediate window, it will select cell B2, because cell B2 is the first non-empty cell from cell D2 to the left.

3. How To Select The Entire Range Cells In VBA.

1. Now, we will tell you how to select the entire range of cells B2 to D4 using the Range.End property.
2. Run the command in the Sheets(“Sheet1”).Range(Sheets(“Sheet1”).Range(“B2”), Sheets(“Sheet1”).Range(“B2”).End(xlToRight).End(xlDown)).select in the VBA immediate window.
3. The line Sheets(“Sheet1”).Range(“B2”) will get the cell B2 object.
4. The line Sheets(“Sheet1”).Range(“B2”).End(xlToRight).End(xlDown) will get the cell D4 object.
5. Then you can find it selects all the cells between B2 to D4 in the Excel worksheet Sheet1.
6. But if cell D4‘s value is empty, how can we select all the cells between cells B2 to D4?

                A            B            C            D            E            F            G            H            I
   1  
   2                       Jerry                    123456789   
   3  
   4                        Tom       999999999   
   5

7. We will tell you how to do it in the next article.