WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA XLUP

VBA XLUP

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

VBA XLUP

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 the 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 the data is very small, we started the room 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 an 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 the last cell in the worksheet instead of the 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 the last used row in excel along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • VBA LOOKUP
  • VBA CDATE Function
  • Message Box (Yes/No) in VBA
  • With VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA XLUP Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More