VBA XLUP

Excel VBA XLUP

One thing you need to keep in mind while writing VBA code is what you do with the regular worksheet and you can replicate the same thing in VBA as well. One of such keyword in VBA coding is “XLUP”, in this article we will show you what this keyword is in VBA coding and how to use it in coding.

How to Use VBA XLUP in Coding?

Following are the examples of excel VBA XLUP.

Example #1 – Move Cells to Deleted Position of Cells

For example, look at the scenario of below data, where you need to delete those colored cells data and more up the below rows data to the above data.

One way of deleting this in the worksheet is to select those cells in which we can simply delete the entire row itself. But here situations are little tricky because I have colored cells in Table 1 when we delete the entire row even Table 2 rows also get deleted, but we don’t want this to happen instead we only need to delete colored rows and below cells should move up the position of the deleted cells.

First, select the colored cells and press Ctrl + Minus Symbol (-) to open “Delete” option.

Shortcut Key to Open “Delete” Option

In the “delete” options window, we have four options, we can choose the action as per our requirement. Since we need to move our cells up for those deleted cells positon, choose “Shift Cell Up”.

We’ll have unchanged Table 2 rows.

This action in VBA requires the use of “XLUP” property to perform a similar set of action in VBA. Now come to the window of VBA editor and start your macro name.

Code:

Sub XLUP_Example()
End Sub

First supply the cell RANGE to be included in this operation. In this action, the first cells to be deleted and move up is “A5: B5” cells.

Code:

Sub XLUP_Example()

  Range ("A5:B5")

End Sub

For this range of cells select “Delete” method.

Code:

Sub XLUP_Example()

  Range("A5:B5").Delete

End Sub

As you can see for “Delete” method, we have one optional argument as [Shift], for this argument we need to enter the argument as “XLUP”.

Code:

Sub XLUP_Example()

  Range("A5:B5").Delete shift:=xlUp

End Sub

Now you can run this code manually or through shortcut excel key F5, to see the result.

As you can see in Table 1, we have row number 6 moved up to 5^th row and on the other hand Table, 2 row (colored) is unaltered, so by using “VBA XLUP” option we can do this operation.

Example #2 – Find Last Used Row by using XLUP

Imagine a situation where you are in the A20th cell (look at below image) and your last-used cell is A14.

Now if you want to choose the last used cell (A14). how will do by using shortcut key???

We would use Ctrl + Up Arrow key to move to the last used cell from the current position.

Shortcut Key to Move to Last used Cell 

So, from the current cell, Ctrl + Up arrow selected the last used cell. Similarly, in VBA coding we use END (XLUP) to perform the same.

Now come back to VBA coding window.

In this window, we will perform the task of finding the last used row in the worksheet. Create a new subprocedure in the VBA window.

Code:

Sub XLUP_Example1()
End Sub

To store the last used row number. define the variable as “LONG” data type.

Code:

Sub XLUP_Example1()

  Dim Last_Row_Number As Long

End Sub

Now for this variable, we will assign the last used row number.

Code:

Sub XLUP_Example1()

  Dim Last_Row_Number As Long
  Last_Row_Number =

End Sub

Now use RANGE object and open this object.

Code:

Sub XLUP_Example1()
  Dim Last_Row_Number As Long
  Last_Row_Number = Range(
End Sub

Now mention the active cell (A20) for RANGE object.

Code:

Sub XLUP_Example1()

  Dim Last_Row_Number As Long
  Range("A14").Select
  Last_Row_Number = Range("A20")

End Sub

Now open END property for supplied range cell.

Code:

Sub XLUP_Example1()
  Dim Last_Row_Number As Long
  Range("A14").Select
  Last_Row_Number = Range("A20").End(
End Sub

As you can see above, we have to arrow key options like “xlDown”, “xlToLeft”, “xlToRight”, “xlUp”. Since we are moving up from the A14 cell choose “VBA XLUP” option.

Code:

Sub XLUP_Example1()

  Dim Last_Row_Number As Long
  Range("A14").Select
  Last_Row_Number = Range("A20").End(xlUp)

End Sub

After moving up from A14 cell we need to mention what we need to do since we need the last used row number I will use ROW property.

Code:

Sub XLUP_Example1()

  Dim Last_Row_Number As Long
  Range("A14").Select
  Last_Row_Number = Range("A20").End(xlUp).Row

End Sub

Now for message box assign the value of variable “Last_Row_Number”.

Code:

Sub XLUP_Example1()

   Dim Last_Row_Number As Long
   Range("A14").Select
   Last_Row_Number = Range("A20").End(xlUp).Row
   MsgBox Last_Row_Number

End Sub

Now you can run this code manually or through shortcut key F5, to see the result.

So message box showing the last used row number as 14, so our last data used row number is A14 cell.

In this case, since data is very small we started from A20 cell but when the data is large we cannot say which cell to take into consideration first, in such case we need to employ a different technique.

We need to use CELLS property, below is the example of the same.

Code:

Sub XLUP_Example2()

  Dim Last_Row_Number As Long
  Last_Row_Number = Cells(Rows.Count, 1).End(xlUp).Row
  MsgBox Last_Row_Number

End Sub

Now you can run this code manually or through shortcut key F5, to see the result.

Instead of RANGE object, I have used CELLS property. Let me explain this in detail for you.

ROW.COUNT this will count how many rows are there in column 1. What this will do is it will take into consideration of the last cell in the worksheet instead of random cell address, in the above case we have used A14 as the random cell address.

Things to Remember about VBA XLUP

• XLUP is the word used in VBA code to replicate the action of “Up Arrow” key in excel.
• VBA XLUP is used to move from active cell to the above cell or last used cell.
• XLUP is generally used along with END property in VBA.

Recommended Articles

This has been a guide to VBA XLUP. Here we discuss how to use XLUP in VBA coding to move cells to a deleted position of cells and to find last used row in excel along with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

• Use of VBA Month Function with Examples
• Resize Property in VBA
• VBA LOOKUP
• VBA CDATE Function
• Delete Sheet in VBA
• Message Box (Yes/No) in VBA
• Goal Seek in VBA
• With VBA