VBA XLUP

Excel VBA XLUP

VBA XlUP is a snippet that is used to find out the last row of the given data set in an excel table. So how this snippet or code or statement does works in VBA. It loops through each instance of the row and adding each instance to the outcome. The loop stops when it reaches the last used cell and then the number of instances the loop is executed is the last row of the data.

So this snippet works as similar to the keyboard shortcut we use in excel worksheet which is CTRL + Down Arrow. This is also known as the range end property method.

How to Use VBA XLUP in Excel?

The following examples will teach us how to use XLUP Property in Excel by using the VBA Code.

Example #1

Let us begin with the first example where we will use the worksheet method of removing data from cells by using keyboard shortcut CTRL + -. When we do this operation on cells which has data then excel gives us certain options like what we want to shift cells or the rows up, left, right or below. For example, let us see the data we have below,

So let us delete the cell range A4:B4 using the XlUP method. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: Now write the subprocedure for VBA XLUP.

Code:

Sub Example1()

End Sub

Step 3: We will use the range property method to decide the range which we want to delete which in this case is A4:B4.

Code:

Sub Example1()

Range("A4:B4")

End Sub

Step 4: We can use the .Delete property method to delete the data from these two cells.

Code:

Sub Example1()

Range("A4:B4").Delete

End Sub

Step 5: Now when we do the same in excel to remove the cell data we get these options.

Code:

Sub Example1()

Range("A4:B4").Delete shift:=xlUp

End Sub

Step 6: Since we want to shift the cells up when the cell data is deleted we will use Shift Statement with the XlUP snippet.

Step 7: Run this code by hitting F5 or Run button. we can see the changes in our data.

Example #2

We can also use the XlUP End snippet to find out the last row in the data. In this example let us use the same data in sheet 1 and find out what the last row is. For this, follow the below steps:

Step 1: In the same module let us begin with our another subprocedure.

Code:

Sub Example2()

End Sub

Step 2: Let us declare a variable that will hold the value for our last row. Since the number of rows in excel exceeds the integer value can hold we will use the long data type.

Code:

Sub Example2()

Dim LRow As Long

End Sub

Step 3: Now in the variable we will store the value of the last row. We can choose any column to find the last row which has data but it is preferred to use the column which has unique index number such as name is unique in this data so we will use column A.

Code:

Sub Example2()

Dim LRow As Long
LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

End Sub

Step 4: Now we can use the Msgbox function to display the value of the last row stored in the variable.

Code:

Sub Example2()

Dim LRow As Long
LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
MsgBox LRow

End Sub

Step 5: Run this code by hitting F5 or Run button placed at the uppermost ribbon of Visual Basic Editor.

Example #3

In the above example, we have used the XlUP Snippet for the last row but we can also find out the last column using a type of this snippet too. In this example, we can use the same data from sheet 1. For this, follow the below steps:

Step 1: So let us declare another procedure in the same module.

Code:

Sub Example3()

End Sub

Step 2: We need to declare two variables one for the row and another for the column.

Code:

Sub Example3()

Dim LRow As Long
Dim lCol As Long

End Sub

Step 3: In the first variable, we will store the value of the last row which is by finding the last used cell in the column.

Code:

Sub Example3()

Dim LRow As Long
Dim lCol As Long
LRow = Cells(Rows.Count, 1).End(xlUp).Row

End Sub

Step 4: Similarly in the second variable we will store the value of the last column by finding the last blank cell in a row.

Code:

Sub Example3()

Dim LRow As Long
Dim lCol As Long
LRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

End Sub

Step 5: Now we can display both last row and the last column using the Msgbox function.

Code:

Sub Example3()

Dim LRow As Long
Dim lCol As Long
LRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "Last Row: " & LRow & vbNewLine & "Last Column: " & lCol

End Sub

Step 6: When we press F8 we can see how this procedure works.

To explain we used column A to find out the last cell which was on the sixth position thus the last row being 6 and in a row where the last cell is the last column thus the column position.

Explanation of VBA XlUP:

As we have learned from the above examples there can be many uses of the XlUP snippet in our day to day life. We can use it to remove data or we can use it to find the last row. But apart from the usage, there are some demerits of using this such as we can find the last row or the last column at a time. But if we are to find the last used cell we have to use two different statements for row and column and by referring them we can find the last used cell.

There are two uses which are explained in the examples above:

1. First is which is used with the shift statement to delete the data from a cell range using the Shift property.
2. Second application is used to find the last row of the property using the End statement.

Things to Remember

There are few things which we need to remember about VBA XlUP and they are:

1. VBA v is a snippet used to find out the last row of the property.
2. This snippet can only be used in a single row.
3. It is pretty much simple to the keyboard shortcuts which involve CTRL + Down Arrow.

Recommended Articles

This is a guide to the VBA XLUP. Here we discuss how to use the XLUP in Excel VBA  for moving the cells toa deleted cell position and finding the last used row along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA XML
2. VBA Filter
3. VBA Login
4. VBA Month