Vba last used row in Excel

As the name implies, this chapter is going to explain about determining the last used row in a sheet.

So, What's is use of this simple calculation? Why should one care about how many rows have been used?

To answer the questions, consider a simple case, where we have a spreadsheet, say, daily sales data, and is updated on daily basis.

So after a month, 30 rows would have been completed and the next day, the data added should be at the 31st row.

When it is done programatically, it does not know the exact number of days, the data is present.

So there must be some logic, where the program should input the data immdiately after the last used row,

This is where, this logic comes to help.

The Syntax:

  1. Sheets().Range("A" & Rows.Count).End(xlUp).Rows

Confusing?? Let us break the code into small parts

  1. Rows.Count
  2. This simply returns the integer 65K, which is the maximum value an Integer can hold.

  1. .End(xlUp).Row
  2. This is the main logic. Here, Excel traverses from Row 65K along Column A until it finds a non-empty value.

the first occurrence of a non-empty value is the last used row from the top.

The following example illustrates this;

  1. Sub lastrow()
  2. Dim lrow As Integer
  3. lrow = Sheets("Q33").Range("A" & Rows.Count).End(xlUp).Row
  4. MsgBox lrow
  5. End Sub

Before running the above code, just check the last used row in this sheet.

The program should return that row number.

The Screenshot of the Editor is as shown below:

excel vba last used row

Note that, only the text I the cells are considered while finding the last used row and not any images.

For example, the above example would return 30 if these two lines are not present.

 

You can find similar Excel Questions and Answer hereunder

1) Vba delete entire row if contains certain text in Excel

2) How to disable ability to insert Rows and Columns in Excel (using VBA)?

3) How can I extract First Name and Last Name from a cell that has Full name?

4) Highlight row of selected cell in Excel

5) How can I find the last used cell in a Column in VBA?

6) How can I quickly navigate to a frequently used field in a voluminous worbook?

7) How can I get the last non-zero value in a row?

8) In a Table , how can I add a Total Row that displays the maximum value in a column of numbers?

9) How do you know which column was used last, Here the explanation to find it with VBA

10) I track a stock on a daily basis and enter the Open, High, Low and Close values for every trading day. In Excel, how can I automatically get High and Low values for the last 10 trading days?

 

Here the previous and next chapter