By Lipis


2008-09-16 10:54:53 8 Comments

How can I find the last row that contains data in a specific column and on a specific sheet?

13 comments

@databyss 2008-09-16 11:23:04

The first line moves the cursor to the last non-empty row in the column. The second line prints that columns row.

Selection.End(xlDown).Select
MsgBox(ActiveCell.Row)

@Jon Crowell 2012-06-02 23:13:23

This doesn't work if there are any blank cells in the column.

@Ashwith Ullal 2015-10-30 11:08:29

Sub test()
    MsgBox Worksheets("sheet_name").Range("A65536").End(xlUp).Row
End Sub

This is looking for a value in column A because of "A65536".

@Sumit Pokhrel 2019-03-27 17:27:43

Last_Row = Range("A1").End(xlDown).Row

Just to verify, let's say you want to print the row number of the last row with the data in cell C1.

Range("C1").Select
Last_Row = Range("A1").End(xlDown).Row
ActiveCell.FormulaR1C1 = Last_Row

@Fionnuala 2008-09-16 11:16:30

How about:

Function GetLastRow(strSheet, strColumn) As Long
    Dim MyRange As Range

    Set MyRange = Worksheets(strSheet).Range(strColumn & "1")
    GetLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row
End Function

Regarding a comment, this will return the row number of the last cell even when only a single cell in the last row has data:

Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

@GSerg 2008-09-16 11:30:48

This function will return wrong results when: - strColumn is a number - there is some data in row 65536 - you are using Excel 2007 with more than 65536 rows

@JimmyPena 2012-06-13 00:31:58

@GSerg You can correct the code, then delete the comment if you like (since it won't apply any longer <g>)

@Scott Marcus 2016-03-13 20:22:20

I believe this will incorrectly report which cell is the last one if the cell had data and then the data was removed.

@Nickolay 2018-04-22 23:48:32

Siddharth Rout's answer to "Error in finding last used cell in VBA" elaborates on these two approaches, noting some problems e.g. with using Rows.Count (see "Find Last Row in a Column" and "Find Last Row in a Sheet"); my answer to that question lists some possibly unintended consequences of using these (namely, not working with autofilter and .Find messing with the defaults in the Excel's Find dialog box).

@Nickolay 2018-04-22 22:53:29

All the solutions relying on built-in behaviors (like .Find and .End) have limitations that are not well-documented (see my other answer for details).

I needed something that:

  • Finds the last non-empty cell (i.e. that has any formula or value, even if it's an empty string) in a specific column
  • Relies on primitives with well-defined behavior
  • Works reliably with autofilters and user modifications
  • Runs as fast as possible on 10,000 rows (to be run in a Worksheet_Change handler without feeling sluggish)
  • ...with performance not falling off a cliff with accidental data or formatting put at the very end of the sheet (at ~1M rows)

The solution below:

  • Uses UsedRange to find the upper bound for the row number (to make the search for the true "last row" fast in the common case where it's close to the end of the used range);
  • Goes backwards to find the row with data in the given column;
  • ...using VBA arrays to avoid accessing each row individually (in case there are many rows in the UsedRange we need to skip)

(No tests, sorry)

' Returns the 1-based row number of the last row having a non-empty value in the given column (0 if the whole column is empty)
Private Function getLastNonblankRowInColumn(ws As Worksheet, colNo As Integer) As Long
    ' Force Excel to recalculate the "last cell" (the one you land on after CTRL+END) / "used range"
    ' and get the index of the row containing the "last cell". This is reasonably fast (~1 ms/10000 rows of a used range)
    Dim lastRow As Long: lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row - 1 ' 0-based

    ' Since the "last cell" is not necessarily the one we're looking for (it may be in a different column, have some
    ' formatting applied but no value, etc), we loop backward from the last row towards the top of the sheet).
    Dim wholeRng As Range: Set wholeRng = ws.Columns(colNo)

    ' Since accessing cells one by one is slower than reading a block of cells into a VBA array and looping through the array,
    ' we process in chunks of increasing size, starting with 1 cell and doubling the size on each iteration, until MAX_CHUNK_SIZE is reached.
    ' In pathological cases where Excel thinks all the ~1M rows are in the used range, this will take around 100ms.
    ' Yet in a normal case where one of the few last rows contains the cell we're looking for, we don't read too many cells.
    Const MAX_CHUNK_SIZE = 2 ^ 10 ' (using large chunks gives no performance advantage, but uses more memory)
    Dim chunkSize As Long: chunkSize = 1
    Dim startOffset As Long: startOffset = lastRow + 1 ' 0-based
    Do ' Loop invariant: startOffset>=0 and all rows after startOffset are blank (i.e. wholeRng.Rows(i+1) for i>=startOffset)
        startOffset = IIf(startOffset - chunkSize >= 0, startOffset - chunkSize, 0)
        ' Fill `vals(1 To chunkSize, 1 To 1)` with column's rows indexed `[startOffset+1 .. startOffset+chunkSize]` (1-based, inclusive)
        Dim chunkRng As Range: Set chunkRng = wholeRng.Resize(chunkSize).Offset(startOffset)
        Dim vals() As Variant
        If chunkSize > 1 Then
            vals = chunkRng.Value2
        Else ' reading a 1-cell range requires special handling <http://www.cpearson.com/excel/ArraysAndRanges.aspx>
            ReDim vals(1 To 1, 1 To 1)
            vals(1, 1) = chunkRng.Value2
        End If

        Dim i As Long
        For i = UBound(vals, 1) To LBound(vals, 1) Step -1
            If Not IsEmpty(vals(i, 1)) Then
                getLastNonblankRowInColumn = startOffset + i
                Exit Function
            End If
        Next i

        If chunkSize < MAX_CHUNK_SIZE Then chunkSize = chunkSize * 2
    Loop While startOffset > 0

    getLastNonblankRowInColumn = 0
End Function

@Phaithoon Jariyanantakul 2017-09-26 06:21:50

Public Function GetLastRow(ByVal SheetName As String) As Integer
    Dim sht As Worksheet
    Dim FirstUsedRow As Integer     'the first row of UsedRange
    Dim UsedRows As Integer         ' number of rows used

    Set sht = Sheets(SheetName)
    ''UsedRange.Rows.Count for the empty sheet is 1
    UsedRows = sht.UsedRange.Rows.Count
    FirstUsedRow = sht.UsedRange.Row
    GetLastRow = FirstUsedRow + UsedRows - 1

    Set sht = Nothing
End Function

sheet.UsedRange.Rows.Count: retrurn number of rows used, not include empty row above the first row used

if row 1 is empty, and the last used row is 10, UsedRange.Rows.Count will return 9, not 10.

This function calculate the first row number of UsedRange plus number of UsedRange rows.

@Nickolay 2018-04-23 00:07:03

I feel this largely duplicates the other answer by newguy. Also note that using Integer instead of Long for the row numbers risks running into an Overflow error with sheets larger than 65k rows.

@Prison Mike 2016-03-13 20:16:06

I would like to add one more reliable way using UsedRange to find the last used row:

lastRow = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count - 1

Similarly to find the last used column you can see this

enter image description here

Result in Immediate Window:

?Sheet1.UsedRange.Row+Sheet1.UsedRange.Rows.Count-1
 21 

@micstr 2016-03-17 12:18:51

Note UsedRange will also pick up formulas which may be a problem if you have formulae dragged down below visible data (or even formatting).

@Prison Mike 2016-03-25 04:58:09

@micstr True but so does every other method will do that.

@user2988717 2014-08-26 15:14:39

Simple and quick:

Dim lastRow as long
Range("A1").select
lastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

Example use:

cells(lastRow,1)="Ultima Linha, Last Row. Youpi!!!!"

'or 

Range("A" & lastRow).Value = "FIM, THE END"

@user2988717 2016-05-23 19:20:10

Or like this 'function getSheetLastRow(sheet2Check as worksheet) lastRow = sheet2Check .Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPre‌​vious).Row getSheetLastRow=lastRow end function'

@Nickolay 2018-04-23 00:02:28

This isn't limited to a specific column as the question requested; it also uses Select and accesses Range/Cells without specifying the worksheet object, which is considered bad style. The "Find Last Row in a Sheet" section of Siddharth Rout's answer to "Error in finding last used cell in VBA" has a better solution, if you feel you must use .Find...

@Dick Kusleika 2008-09-16 16:34:08

Public Function LastData(rCol As Range) As Range    
    Set LastData = rCol.Find("*", rCol.Cells(1), , , , xlPrevious)    
End Function

Usage: ?lastdata(activecell.EntireColumn).Address

@Jon Fournier 2008-09-16 15:21:00

You should use the .End(xlup) but instead of using 65536 you might want to use:

sheetvar.Rows.Count

That way it works for Excel 2007 which I believe has more than 65536 rows

@Greg Podesta 2009-06-07 18:53:01

Here's a solution for finding the last row, last column, or last cell. It addresses the A1 R1C1 Reference Style dilemma for the column it finds. Wish I could give credit, but can't find/remember where I got it from, so "Thanks!" to whoever it was that posted the original code somewhere out there.

Sub Macro1
    Sheets("Sheet1").Select
    MsgBox "The last row found is: " & Last(1, ActiveSheet.Cells)
    MsgBox "The last column (R1C1) found is: " & Last(2, ActiveSheet.Cells)
    MsgBox "The last cell found is: " & Last(3, ActiveSheet.Cells)
    MsgBox "The last column (A1) found is: " & Last(4, ActiveSheet.Cells)
End Sub

Function Last(choice As Integer, rng As Range)
' 1 = last row
' 2 = last column (R1C1)
' 3 = last cell
' 4 = last column (A1)
    Dim lrw As Long
    Dim lcol As Integer

    Select Case choice
    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       LookAt:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        Last = Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0
    Case 4:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0
        Last = R1C1converter("R1C" & Last, 1)
        For i = 1 To Len(Last)
            s = Mid(Last, i, 1)
            If Not s Like "#" Then s1 = s1 & s
        Next i
        Last = s1

    End Select

End Function

Function R1C1converter(Address As String, Optional R1C1_output As Integer, Optional RefCell As Range) As String
    'Converts input address to either A1 or R1C1 style reference relative to RefCell
    'If R1C1_output is xlR1C1, then result is R1C1 style reference.
    'If R1C1_output is xlA1 (or missing), then return A1 style reference.
    'If RefCell is missing, then the address is relative to the active cell
    'If there is an error in conversion, the function returns the input Address string
    Dim x As Variant
    If RefCell Is Nothing Then Set RefCell = ActiveCell
    If R1C1_output = xlR1C1 Then
        x = Application.ConvertFormula(Address, xlA1, xlR1C1, , RefCell) 'Convert A1 to R1C1
    Else
        x = Application.ConvertFormula(Address, xlR1C1, xlA1, , RefCell) 'Convert R1C1 to A1
    End If
    If IsError(x) Then
        R1C1converter = Address
    Else
        'If input address is A1 reference and A1 is requested output, then Application.ConvertFormula
        'surrounds the address in single quotes.
        If Right(x, 1) = "'" Then
            R1C1converter = Mid(x, 2, Len(x) - 2)
        Else
            x = Application.Substitute(x, "$", "")
            R1C1converter = x
        End If
    End If
End Function

@GSerg 2008-09-16 11:14:22

function LastRowIndex(byval w as worksheet, byval col as variant) as long
  dim r as range

  set r = application.intersect(w.usedrange, w.columns(col))
  if not r is nothing then
    set r = r.cells(r.cells.count)

    if isempty(r.value) then
      LastRowIndex = r.end(xlup).row
    else
      LastRowIndex = r.row
    end if
  end if
end function

Usage:

? LastRowIndex(ActiveSheet, 5)
? LastRowIndex(ActiveSheet, "AI")

@gdelfino 2015-03-26 14:33:54

Is the check for isempty(r.value) really needed? Shouldn't it always have a value?

@GSerg 2015-03-26 14:57:39

@gdelfino Yes it is. E.g. column A has values in rows 1-10, column B has values in rows 1-8. UsedRange will be A1:B10, the intersection with B:B will be B1:B10, the last cell is B10 and it is empty.

@Nickolay 2018-04-22 23:57:21

Why the extra complexity of getting the last row of UsedRange instead of starting with the very last row of the sheet (sht.Rows.Count) if you use .end(xlup) anyway? I believe the way you're getting the last row will fail if the UsedRange doesn't start at the first row (i.e. if the first row is empty).

@GSerg 2018-04-23 00:23:43

@Nickolay Because starting with the very last row of the sheet is based on the false assumption that the last row of the sheet is empty. It often is, but not always. I am not happy with false assumptions like that. It will jump up past the original UsedRange only if the column is completely empty, which is correct behaviour given the only two variables that define the list here are the worksheet and the column number, which is exactly how the OP worded the problem.

@Nickolay 2018-04-23 00:27:02

No, that's the argument in favor of the if isempty(r.value) check -- I applaud that. You could start with the very last row, still make that check, but save 5 lines before that (while removing the wrong counting logic) -- and end up with a more robust version of "Find Last Row in a Column" section of Siddharth Rout's answer to "Error in finding last used cell in VBA" (still not dealing with autofilter, but it's OK for some use-cases). Unless this is an optimization, which I wouldn't think is necessary, but I thought I would ask first.

@GSerg 2018-04-23 00:43:26

@Nickolay It was almost ten years ago that I wrote that code. I can't tell you exactly what I had in mind back then, but from the looks of it, because the function is supposed to return the last unusable row (to which you add 1 to get the first usable row), I made it to return 0 in case the column in question is not a part of UsedRange and so is completely empty - zero then is a valid answer because you add + 1 and end up writing the new data to the first row, which is correct, and in that case .End is not called.

@GSerg 2018-04-23 00:44:11

@Nickolay Then again, if the column is completely empty, but is a part of UsedRange, it will return 1 as opposed to 0, which is not consistent, so you have a point.

@Galwegian 2008-09-16 10:56:52

Function LastRow(rng As Range) As Long
    Dim iRowN As Long
    Dim iRowI As Long
    Dim iColN As Integer
    Dim iColI As Integer
    iRowN = 0
    iColN = rng.Columns.count
    For iColI = 1 To iColN
        iRowI = rng.Columns(iColI).Offset(65536 - rng.Row, 0).End(xlUp).Row
        If iRowI > iRowN Then iRowN = iRowI
    Next
    LastRow = iRowN
End Function 

Related Questions

Sponsored Content

7 Answered Questions

[SOLVED] How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

  • 2014-03-20 19:09:13
  • Portland Runner
  • 787893 View
  • 531 Score
  • 7 Answer
  • Tags:   regex excel vba

3 Answered Questions

[SOLVED] Find last row of a specific range of columns

  • 2016-05-06 16:16:27
  • carlos_cs
  • 1574 View
  • 0 Score
  • 3 Answer
  • Tags:   excel excel-vba vba

5 Answered Questions

[SOLVED] Excel VBA- Finding the last column with data

  • 2012-08-13 00:33:45
  • Neat Machine
  • 325103 View
  • 46 Score
  • 5 Answer
  • Tags:   excel excel-vba vba

2 Answered Questions

[SOLVED] How do you find the last row number in column VBA?

  • 2018-04-20 04:54:50
  • SaladSnake
  • 3497 View
  • -2 Score
  • 2 Answer
  • Tags:   vba excel-vba excel

2 Answered Questions

[SOLVED] Excel VBA - Finding Last Non-Empty Row Based on Column Header

3 Answered Questions

[SOLVED] Last column of a specific row in Excel

  • 2018-03-22 15:06:57
  • user9351236
  • 1829 View
  • 0 Score
  • 3 Answer
  • Tags:   excel vba excel-vba

0 Answered Questions

Find last populated column in each row

2 Answered Questions

[SOLVED] Find last row of specific range of data excel

1 Answered Questions

[SOLVED] Finding last column and then finding the last row in that column

1 Answered Questions

Get row number of lowest/largest row number containing data?

  • 2012-07-04 13:54:52
  • mezamorphic
  • 1007 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba

Sponsored Content