Better way to find last used row

Question

I am trying to find the last row the same way I found the last column:

Sheets("Sheet2").Cells(1,Sheets("Sheet2").Columns.Count).End(xlToLeft).Column

I know this way but it is not as helpful as the prior would be:

u = Sheets("Sheet1").Range("A65536").End(xlUp).Row

I tried:

Sheets("Sheet2").Cells(Sheets("Sheet2",1).Rowa.Count).End(xlToUP).Column

Synopsis: I would like the below way for last row.

Sheets("Sheet2").Cells(1,Sheets("Sheet2").Columns.Count).End(xlToLeft).Column

Answer 1

Problems with normal methods

Account for Blank Rows / Columns - If you have blank rows or columns at the beginning of your data then methods like UsedRange.Rows.Count and UsedRange.Columns.Count will skip over these blank rows (although they do account for any blank rows / columns that might break up the data), so if you refer to ThisWorkbook.Sheets(1).UsedRange.Rows.Count you will skip lines in cases where there are blank rows at the top of your sheet, for example on this sheet:

This will skip the top row from the count and return 11:

ThisWorkbook.Sheets(1).UsedRange.Rows.Count

This code will include the blank row and return 12 instead:

ThisWorkbook.Sheets(1).UsedRange.Cells(ThisWorkbook.Sheets(1).UsedRange.Rows.Count, 1).Row

The same issue applies to columns.

Full Sheets - Identifying the last row or column can be difficult if your sheet is full (this only matters if either your data contains over a million lines or might have values in the final rows or columns of your data). For example, if you use xlEndUp or similar and the cell you're referring to is populated then the code will skip over data, in extreme cases your entire data set can be skipped if for example the data continues from the last row of the sheet (where you start your xlEndUp) solidly up to the first row (in this case the result would be 1).

'This code works, but... 
'Will not function as intended if there is data in the cell you start with (Cell A:1048576).
Dim Sht1 as Range: Set Sht1 = ThisWorkbook.Sheets(1)
Sht1.Cells(Sht1.Rows.Count, 1).End(xlUp).Row

Columns with blank rows - The above code also assumes that your data extends the entire way down column 1, if you have blank entries in column 1 you may lose rows as the code will find the first filled row from the bottom only for column 1.

Unnecessary Looping - Self explanatory, best to avoid looping where possible as if you're dealing with a lot of data and repeating the looping process often it can slow down your code.

Solution

Note that this is targeted at finding the last "Used" Row or Column on an entire sheet, this doesn't work if you just want the last cell in a specific range.

I've setup some Functions here

Private Function GetLastRow(Sheet As Worksheet)
'Gets last used row # on sheet.
GetLastRow = Sheet.UsedRange.Cells(Sheet.UsedRange.Rows.Count, 1).Row
End Function

Private Function GetLastCol(Sheet As Worksheet)
'Gets last used column # on sheet.
GetLastCol = Sheet.UsedRange.Cells(1, Sheet.UsedRange.Columns.Count).Column
End Function

Examples of calling these Functions:

Sub CallFunctions()
    'Define the Target Worksheet  we're interested in:
    Dim Sht1 As Worksheet: Set Sht1 = ThisWorkbook.Sheets(1)
    'Print the last row and column numbers:
    Debug.Print "Last Row = "; GetLastRow(Sht1)
    Debug.Print "Last Col = "; GetLastCol(Sht1)
End Sub

Answer 2

This gives you the last used row in a specified column.

Optionally you can specify the worksheet, otherwise it will take the active sheet.

Function getLastRow(col As Integer, Optional ws As Worksheet) As Long

    If ws Is Nothing Then Set ws = ActiveSheet
    
    If ws.Cells(ws.Rows.Count, col).Value <> "" Then
        getLastRow = ws.Cells(ws.Rows.Count, col).Row
        Exit Function
    End If

    getLastRow = ws.Cells(Rows.Count, col).End(xlUp).Row

    If shtRowCount = 1 Then
        If ws.Cells(1, col) = "" Then
            getLastRow = 0
        Else
            getLastRow = 1
        End If
    End If

End Function

Sub test()

    Dim lgLastRow As Long
    lgLastRow = getLastRow(2) 'Column B

End Sub

Answer 3

I use the following function extensively. As pointed out above, using other methods can sometimes give inaccurate results due to used range updates, gaps in the data, or different columns having different row counts.

Example of use:

lastRow=FindRange("Sheet1","A1:A1000")

would return the last occupied row number of the entire range. You can specify any range you want from single columns to random rows, eg FindRange("Sheet1","A100:A150")

Public Function FindRange(inSheet As String, inRange As String) As Long
    Set fr = ThisWorkbook.Sheets(inSheet).Range(inRange).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not fr Is Nothing Then FindRange = fr.row Else FindRange = 0
End Function

Answer 4

I preferred search last blank cell:

Il you want last empty cell of column you can do that

Dim sh as Worksheet, r as range
set sh = ActiveWorksheet 'if you want an other it's possible

'find a value 
'Columns("A:D") 'to check on multiple columns 
Set r = sh.Columns("A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'no value return first row
If r Is Nothing Then Set r = sh.Cells(1, "A") Else Set r = sh1.Cells(r.Row + 1, "A")

If this is to insert new row, find on multiple columns is a good choice because first column can contains less rows than next columns

Answer 5

This function should do the trick if you want to specify a particular sheet. I took the solution from user6432984 and modified it to not throw any errors. I am using Excel 2016 so it may not work for older versions:

Function findLastRow(ByVal inputSheet As Worksheet) As Integer
    findLastRow = inputSheet.cellS(inputSheet.Rows.Count, 1).End(xlUp).Row
End Function

This is the code to run if you are already working in the sheet you want to find the last row of:

Dim lastRow as Integer
lastRow = cellS(Rows.Count, 1).End(xlUp).Row

Answer 6

I use this routine to find the count of data rows. There is a minimum of overhead required, but by counting using a decreasing scale, even a very large result requires few iterations. For example, a result of 28,395 would only require 2 + 8 + 3 + 9 + 5, or 27 times through the loop, instead of a time-expensive 28,395 times.

Even were we to multiply that by 10 (283,950), the iteration count is the same 27 times.

Dim lWorksheetRecordCountScaler as Long
Dim lWorksheetRecordCount as Long

Const sDataColumn = "A"   '<----Set to column that has data in all rows (Code, ID, etc.)

    'Count the data records
    lWorksheetRecordCountScaler = 100000  'Begin by counting in 100,000-record bites
    lWorksheetRecordCount = lWorksheetRecordCountScaler

    While lWorksheetRecordCountScaler >= 1

        While Sheets("Sheet2").Range(sDataColumn & lWorksheetRecordCount + 2).Formula > " "
            lWorksheetRecordCount = lWorksheetRecordCount + lWorksheetRecordCountScaler
        Wend

        'To the beginning of the previous bite, count 1/10th of the scale from there
        lWorksheetRecordCount = lWorksheetRecordCount - lWorksheetRecordCountScaler
        lWorksheetRecordCountScaler = lWorksheetRecordCountScaler / 10

    Wend

    lWorksheetRecordCount = lWorksheetRecordCount + 1   'Final answer

Answer 7

This is the best way I've seen to find the last cell.

MsgBox ActiveSheet.UsedRage.SpecialCells(xlCellTypeLastCell).Row

One of the disadvantages to using this is that it's not always accurate. If you use it then delete the last few rows and use it again, it does not always update. Saving your workbook before using this seems to force it to update though.

Using the next bit of code after updating the table (or refreshing the query that feeds the table) forces everything to update before finding the last row. But, it's been reported that it makes excel crash. Either way, calling this before trying to find the last row will ensure the table has finished updating first.

Application.CalculateUntilAsyncQueriesDone

Another way to get the last row for any given column, if you don't mind the overhead.

Function GetLastRow(col, row)
    ' col and row are where we will start.
    ' We will find the last row for the given column.
    Do Until ActiveSheet.Cells(row, col) = ""
        row = row + 1
    Loop
    GetLastRow = row
End Function

Answer 8

How is this?

dim rownum as integer
dim colnum as integer
dim lstrow as integer
dim lstcol as integer
dim r as range

'finds the last row

lastrow = ActiveSheet.UsedRange.Rows.Count

'finds the last column

lastcol = ActiveSheet.UsedRange.Columns.Count

'sets the range

set r = range(cells(rownum,colnum), cells(lstrow,lstcol))

Answer 9

You should use a with statement to qualify both your Rows and Columns counts. This will prevent any errors while working with older pre 2007 and newer 2007 Excel Workbooks.

Last Column

With Sheets("Sheet2")
    .Cells(1, .Columns.Count).End(xlToLeft).Column
End With 

Last Row

With Sheets("Sheet2")
    .Range("A" & .Rows.Count).End(xlUp).Row
End With 

Or

With Sheets("Sheet2")
    .Cells(.Rows.Count, 1).End(xlUp).Row
End With