EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home VBA VBA Resources VBA Coding VBA XLUP

VBA XLUP

Madhuri Thakur
Article byMadhuri Thakur

Updated June 13, 2023

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 adds each instance to the outcome. The loop stops when it reaches the last used cell, and the number of instances the loop executes is the last row of the data.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

So this snippet works similarly to the keyboard shortcut we use in the Excel worksheet: 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.

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

Example #1

Let us begin with the first example, where we will use the worksheet method of removing data from cells using the keyboard shortcut CTRL + -. When we do this operation on cells with data, 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,

VBA XLUP

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.

Insert Module

Step 2: Now write the sub-procedure for VBA XLUP.

Code:

Sub Example1()

End Sub

VBA XLUP Example1-2

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

Code:

Sub Example1()

Range("A4:B4")

End Sub

Range Example1-3

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

Delete property Example1-4

Step 5: 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

VBA XLUP Example1-5

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.

Cell Data Example1-6

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

Delete Cell Example1-7

Example #2

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

Step 1: Let us begin with another sub-procedure in the same Module.

Code:

Sub Example2()

End Sub

VBA XLUP Example2-1

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, we will use the long data type.

Code:

Sub Example2()

Dim LRow As Long

End Sub

VBA XLUP Example2-2

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 with data, but using the column with a unique index number, such as the unique name in this data, is preferred, so we will use column A.

Code:

Sub Example2()

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

End Sub

VBA XLUP Example2-3

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

Msgbox function Example2-4

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

VBA XLUP Example2-5

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

VBA XLUP Example3-1

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

VBA XLUP Example3-2

Step 3: In the first variable, we will store the value of the last row 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

VBA XLUP Example3-3

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

VBA XLUP Example3-4

Step 5: Now, we can display both the 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

VBA XLUP Example3-5

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

VBA XLUP Example3-6

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

Explanation of VBA XlUP:

As we have learned from the above examples, the XlUP snippet can be used daily. 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 to them, we can find the last used cell.

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

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

Things to Remember

There are a few things that 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 simple to the keyboard shortcuts involving CTRL + Down Arrow.

Recommended Articles

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

  1. VBA XML
  2. VBA Filter
  3. Excel VBA MsgBox
  4. VBA Month
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

EDUCBA

Download VBA XLUP Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW