How to find last row from bottom up in Excel [VBA & Office Scripts]

This article guides you on how to find the last row in your Excel spreadsheet. Manipulating spreadsheet data is a task encountered by most developers. Whether it is for Data analysis or simple data organization, knowing how to navigate spreadsheets is essential.  The last used row refers to the final row in your Excel spreadsheet that contains data, which is not necessarily the last row that the Excel application can handle.  Excel has a limit of 1,048,576 rows.

You may need to find the last row when appending multiple worksheet data into one sheet or when you work with sales data, where every month you will receive new data and need to continuously add it to your data set.  You need to identify the last row and add the new data for your calculations and data processing.

You can use the keyboard shortcuts Ctrl + Down Arrow to find the last row in Excel manually. This will take you directly to the last row of data in your spreadsheet. If you have multiple sections of data separated by empty rows, this method will stop at the first empty cell it finds.

In this blog post, you will learn VBA code and Office Scripts to find last row and last column in detail.  It will be very helpful for creating a dashboard, processing data, and automating the MIS report. Let’s get started!

Find the Last Row with Excel VBA

You can use the Visual Basic for Applications script to find the last row in Excel for your automation and data analysis.  The below VBA sub-procedure will place your cursor in the last row of the first column A.

Sub FindLastRow()

    ' Define variable LstRow for the last row
    Dim LstRow As Long

    ' Find the last row
    LstRow = Cells(Rows.Count, "A").End(xlUp).Row

    ' Select the last row
    Range("A" & LstRow).Select

End Sub

The subroutine has the following steps that find and select the last row in Column A

1. Declare a variable named LstRow of type Long, which can store large integer values.
2. Assign the value of the last row in column A to the variable LstRow. This is done by using the Cells property to access the cell at the intersection of the last row (Rows.Count) and column A, and then using the End(xlUp) to move up from that cell until a non-empty cell is found. The Row property returns the row number of that cell.
3. Select the cell in column A by using the Range property to access the cell at the address A & LstRow, where & is the concatenation operator that joins the letter A with the value of LstRow. The Select method activates that cell.

Steps to Run the FindLastRow macro.

1. Go to Data Sheet
2. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
3. Choose the FindLastRow Macro and then Press Run.

Use the below VBA procedure to find the Last Column in Excel. It will place your cursor in the first row of the last column.

Sub FindLastCol()
    
    ' Define variable LstCol for the last column
    Dim LstCol As Long

    ' Find the last column
    LstCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    ' Select the last column in the first row
    Cells(1, LstCol).Select

End Sub

Note: you must save the workbook as a macro-enabled workbook (*.xlsm) to retain the VBA code in the workbook.

Find the Last Row with Excel Office Scripts

Office Scripts is a programming feature of Excel that allows you to automate tasks and workflows. Excel allows you to record your actions and generate a script that can be edited and customized. You can also write your own scripts from scratch.

function main(workbook: ExcelScript.Workbook) {

  // Assign Data sheet to the variable sourceWorkSheet
  const sourceWorkSheet = workbook.getWorksheet("Data");

  // Get used Range and assigned to usdRange
  const usdRange = sourceWorkSheet.getUsedRange(true);

  // Find last used row and assign the index to the lstRow Variable
  const lstRow = usdRange.getLastRow().getRowIndex()

  // Below line of syntax to find the last used column
  //const lstCol = usdRange.getLastColumn().getColumnIndex()

  // Select the lstRow in the first column of the Data WorkSheet
  usdRange.getCell(lstRow,0).select()

}

 Understand and Identify the Variables

First, you have the worksheet variable, which basically holds the worksheet data. For example, you have a workbook variable.

const sourceWorkSheet = workbook.getWorksheet("Data");

From the workbook variable, you perform a getWorksheet() function to fetch the desired data tab Data. So, you will have something like the below syntax.

At this point, sourceWorkSheet will be housing the worksheet that you want to manipulate.

const usdRange = sourceWorkSheet.getUsedRange(true);

You use the getUsedRange() function to identify used ranges in your spreadsheet. You can use it without any parameters or pass a true boolean. Passing true would mean the function will only consider cells with actual values, ignoring any formats.

Find the Last Row or Column

With the usdRange variable, you can easily find out first and last rows or columns. You used these functions to find the last used row and column in Excel.

getLastRow(): fetches the last row in the used range.

getLastColumn(): fetches the last column in the used range.

Note that Office Scripts indexes start from zero. So, an index of 20 corresponds to the 21st row.

Here’s your code to get a Row or Column index in office scripts

const lstRow = usdRange.getLastRow().getRowIndex();

Use the below syntax if you need to get the last column in Excel

const lstCol = usdRange.getLastColumn().getColumnIndex()

you used the getRowIndex() or getColumnIndex() methods to get the row and column index.

Follow the below steps to execute the Find Last Row Office script in your Excel

1. Select any cell in the worksheet.
2. Go to the Automate menu.
3. Select the Find Last Row Script – to open the Code editor on the right side of your Excel application.
4. Press Run 

Conclusion

There are various ways to process your data in Excel. You might want to know the last row or column that contains data so that you can apply the formatting to the correct range. Also it can help you to set up the print area correctly when printing a worksheet.

Suppose you are working with large datasets, and you have a formula to refer only to the used rows. Knowing the last row or column that contains data will help you to avoid processing unnecessary data and improve the performance of your analysis.

You learned both VBA and Office Scripts methods to find the last used row and column in Excel. It works well in your automation tasks for example to copy data from one worksheet to another, but only copy the data up to the last used row or column.

Hope this article helps you, do you have used any of these methods to Find Last Row in Excel? Let me know in the comments section!