WallStreetMojo

WallStreetMojo

Home » Learn VBA » VBA XLUP

VBA XLUP

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 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?

The following are the examples of excel VBA XLUP.

You can download this VBA XLUP Excel Template here – VBA XLUP Excel Template

Example #1 – Move Cells to Deleted Position of Cells

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

VBA XLUP Example 1

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 the “Delete” option.

Shortcut Key to Open “Delete” Option

Shortcut Key to Open Delete Option

Example 1.1

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”.

VBA XLUP Example 1.2

We’ll have unchanged Table 2 rows.

Example 1.3

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

Code:

Sub XLUP_Example()
End Sub

VBA XLUP Example 1.4

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

Example 1.5.0

For this range of cells select the “Delete” method.

Code:

Sub XLUP_Example()

  Range("A5:B5").Delete

End Sub

VBA XLUP Example 1.6.0

As you can see for the “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

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

VBA XLUP Example 1

As you can see in Table 1, we have row number 6 moved up to 5th row and on the other hand Table, 2 row (colored) is unaltered, so by using the “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.

VBA XLUP Example 1.8

Now if you want to choose the last used cell (A14). how will you do by using a 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 

Shortcut Key to Move to Last used Cell 

VBA XLUP Example 2.10

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

Example 2

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

Code:

Sub XLUP_Example1()

  Dim Last_Row_Number As Long

End Sub

VBA XLUP Example 2.1.0
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

VBA XLUP Example 2.2.0

Now use RANGE object and open this object.

Code:

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

VBA XLUP Example 2.3

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

VBA XLUP Example 2.4.0.1

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

Example 2.5.0

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 the “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

VBA XLUP Example 2.6.0.1
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

VBA XLUP Example 2.7.0.2

Now for the 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

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

VBA XLUP Example 2

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 cases 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.

VBA XLUP Examaple 3

Instead of a RANGE object, I have used CELLS property. Let me explain this in detail to 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 the “Up Arrow” key in excel.
  • VBA XLUP is used to move from active cells 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 –

0 Shares
Share
Tweet
Share