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

[{'answer_id': 71197, 'author': 'Galwegian', 'author_id': 3201, 'author_profile': 'https://Stackoverflow.com/users/3201', 'pm_score': -1, 'selected': False, 'text': '<pre><code>Function LastRow(rng As Range) As Long\n    Dim iRowN As Long\n    Dim iRowI As Long\n    Dim iColN As Integer\n    Dim iColI As Integer\n    iRowN = 0\n    iColN = rng.Columns.count\n    For iColI = 1 To iColN\n        iRowI = rng.Columns(iColI).Offset(65536 - rng.Row, 0).End(xlUp).Row\n        If iRowI &gt; iRowN Then iRowN = iRowI\n    Next\n    LastRow = iRowN\nEnd Function \n</code></pre>\n'}, {'answer_id': 71296, 'author': 'GSerg', 'author_id': 11683, 'author_profile': 'https://Stackoverflow.com/users/11683', 'pm_score': 3, 'selected': False, 'text': '<pre><code>function LastRowIndex(byval w as worksheet, byval col as variant) as long\n  dim r as range\n\n  set r = application.intersect(w.usedrange, w.columns(col))\n  if not r is nothing then\n    set r = r.cells(r.cells.count)\n\n    if isempty(r.value) then\n      LastRowIndex = r.end(xlup).row\n    else\n      LastRowIndex = r.row\n    end if\n  end if\nend function\n</code></pre>\n\n<p>Usage:</p>\n\n<pre><code>? LastRowIndex(ActiveSheet, 5)\n? LastRowIndex(ActiveSheet, "AI")\n</code></pre>\n'}, {'answer_id': 71310, 'author': 'Fionnuala', 'author_id': 2548, 'author_profile': 'https://Stackoverflow.com/users/2548', 'pm_score': 7, 'selected': True, 'text': '<p>How about:</p>\n\n<pre><code>Function GetLastRow(strSheet, strColumn) As Long\n    Dim MyRange As Range\n\n    Set MyRange = Worksheets(strSheet).Range(strColumn &amp; "1")\n    GetLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row\nEnd Function\n</code></pre>\n\n<p>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:</p>\n\n<pre><code>Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row\n</code></pre>\n'}, {'answer_id': 71349, 'author': 'databyss', 'author_id': 9094, 'author_profile': 'https://Stackoverflow.com/users/9094', 'pm_score': -1, 'selected': False, 'text': '<p>The first line moves the cursor to the last non-empty row in the column.  The second line prints that columns row.</p>\n\n<pre><code>Selection.End(xlDown).Select\nMsgBox(ActiveCell.Row)\n</code></pre>\n'}, {'answer_id': 73489, 'author': 'Jon Fournier', 'author_id': 5106, 'author_profile': 'https://Stackoverflow.com/users/5106', 'pm_score': 4, 'selected': False, 'text': '<p>You should use the <code>.End(xlup)</code> but instead of using 65536 you might want to use:</p>\n\n<pre><code>sheetvar.Rows.Count\n</code></pre>\n\n<p>That way it works for Excel 2007 which I believe has more than 65536 rows</p>\n'}, {'answer_id': 74282, 'author': 'Dick Kusleika', 'author_id': 4280, 'author_profile': 'https://Stackoverflow.com/users/4280', 'pm_score': 2, 'selected': False, 'text': '<pre><code>Public Function LastData(rCol As Range) As Range    \n    Set LastData = rCol.Find("*", rCol.Cells(1), , , , xlPrevious)    \nEnd Function\n</code></pre>\n\n<p>Usage: <code>?lastdata(activecell.EntireColumn).Address</code></p>\n'}, {'answer_id': 962530, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code>Sub Macro1\n    Sheets("Sheet1").Select\n    MsgBox "The last row found is: " &amp; Last(1, ActiveSheet.Cells)\n    MsgBox "The last column (R1C1) found is: " &amp; Last(2, ActiveSheet.Cells)\n    MsgBox "The last cell found is: " &amp; Last(3, ActiveSheet.Cells)\n    MsgBox "The last column (A1) found is: " &amp; Last(4, ActiveSheet.Cells)\nEnd Sub\n\nFunction Last(choice As Integer, rng As Range)\n\' 1 = last row\n\' 2 = last column (R1C1)\n\' 3 = last cell\n\' 4 = last column (A1)\n    Dim lrw As Long\n    Dim lcol As Integer\n\n    Select Case choice\n    Case 1:\n        On Error Resume Next\n        Last = rng.Find(What:="*", _\n                        After:=rng.Cells(1), _\n                        LookAt:=xlPart, _\n                        LookIn:=xlFormulas, _\n                        SearchOrder:=xlByRows, _\n                        SearchDirection:=xlPrevious, _\n                        MatchCase:=False).Row\n        On Error GoTo 0\n\n    Case 2:\n        On Error Resume Next\n        Last = rng.Find(What:="*", _\n                        After:=rng.Cells(1), _\n                        LookAt:=xlPart, _\n                        LookIn:=xlFormulas, _\n                        SearchOrder:=xlByColumns, _\n                        SearchDirection:=xlPrevious, _\n                        MatchCase:=False).Column\n        On Error GoTo 0\n\n    Case 3:\n        On Error Resume Next\n        lrw = rng.Find(What:="*", _\n                       After:=rng.Cells(1), _\n                       LookAt:=xlPart, _\n                       LookIn:=xlFormulas, _\n                       SearchOrder:=xlByRows, _\n                       SearchDirection:=xlPrevious, _\n                       MatchCase:=False).Row\n        lcol = rng.Find(What:="*", _\n                        After:=rng.Cells(1), _\n                        LookAt:=xlPart, _\n                        LookIn:=xlFormulas, _\n                        SearchOrder:=xlByColumns, _\n                        SearchDirection:=xlPrevious, _\n                        MatchCase:=False).Column\n        Last = Cells(lrw, lcol).Address(False, False)\n        If Err.Number &gt; 0 Then\n            Last = rng.Cells(1).Address(False, False)\n            Err.Clear\n        End If\n        On Error GoTo 0\n    Case 4:\n        On Error Resume Next\n        Last = rng.Find(What:="*", _\n                        After:=rng.Cells(1), _\n                        LookAt:=xlPart, _\n                        LookIn:=xlFormulas, _\n                        SearchOrder:=xlByColumns, _\n                        SearchDirection:=xlPrevious, _\n                        MatchCase:=False).Column\n        On Error GoTo 0\n        Last = R1C1converter("R1C" &amp; Last, 1)\n        For i = 1 To Len(Last)\n            s = Mid(Last, i, 1)\n            If Not s Like "#" Then s1 = s1 &amp; s\n        Next i\n        Last = s1\n\n    End Select\n\nEnd Function\n\nFunction R1C1converter(Address As String, Optional R1C1_output As Integer, Optional RefCell As Range) As String\n    \'Converts input address to either A1 or R1C1 style reference relative to RefCell\n    \'If R1C1_output is xlR1C1, then result is R1C1 style reference.\n    \'If R1C1_output is xlA1 (or missing), then return A1 style reference.\n    \'If RefCell is missing, then the address is relative to the active cell\n    \'If there is an error in conversion, the function returns the input Address string\n    Dim x As Variant\n    If RefCell Is Nothing Then Set RefCell = ActiveCell\n    If R1C1_output = xlR1C1 Then\n        x = Application.ConvertFormula(Address, xlA1, xlR1C1, , RefCell) \'Convert A1 to R1C1\n    Else\n        x = Application.ConvertFormula(Address, xlR1C1, xlA1, , RefCell) \'Convert R1C1 to A1\n    End If\n    If IsError(x) Then\n        R1C1converter = Address\n    Else\n        \'If input address is A1 reference and A1 is requested output, then Application.ConvertFormula\n        \'surrounds the address in single quotes.\n        If Right(x, 1) = "\'" Then\n            R1C1converter = Mid(x, 2, Len(x) - 2)\n        Else\n            x = Application.Substitute(x, "$", "")\n            R1C1converter = x\n        End If\n    End If\nEnd Function\n</code></pre>\n'}, {'answer_id': 25509398, 'author': 'user2988717', 'author_id': 2988717, 'author_profile': 'https://Stackoverflow.com/users/2988717', 'pm_score': 3, 'selected': False, 'text': '<p>Simple and quick:</p>\n\n<pre><code>Dim lastRow as long\nRange("A1").select\nlastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row\n</code></pre>\n\n<p>Example use:</p>\n\n<pre><code>cells(lastRow,1)="Ultima Linha, Last Row. Youpi!!!!"\n\n\'or \n\nRange("A" &amp; lastRow).Value = "FIM, THE END"\n</code></pre>\n'}, {'answer_id': 33434570, 'author': 'Ashwith Ullal', 'author_id': 1534035, 'author_profile': 'https://Stackoverflow.com/users/1534035', 'pm_score': -1, 'selected': False, 'text': '<pre><code>Sub test()\n    MsgBox Worksheets("sheet_name").Range("A65536").End(xlUp).Row\nEnd Sub\n</code></pre>\n\n<p>This is looking for a value in column <code>A</code> because of <code>"A65536"</code>.</p>\n'}, {'answer_id': 35975280, 'author': 'Stupid_Intern', 'author_id': 5398127, 'author_profile': 'https://Stackoverflow.com/users/5398127', 'pm_score': 0, 'selected': False, 'text': '<p>I would like to add one more reliable way using <code>UsedRange</code> to find the last used row:</p>\n\n<pre><code>lastRow = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count - 1\n</code></pre>\n\n<p>Similarly to find the last used column you can <a href="https://stackoverflow.com/questions/11926972/excel-vba-finding-the-last-column-with-data/35945397#35945397">see this</a></p>\n\n<p><a href="https://i.stack.imgur.com/46aa0.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/46aa0.jpg" alt="enter image description here"></a></p>\n\n<p>Result in Immediate Window:</p>\n\n<pre><code>?Sheet1.UsedRange.Row+Sheet1.UsedRange.Rows.Count-1\n 21 \n</code></pre>\n'}, {'answer_id': 46419169, 'author': 'Phaithoon Jariyanantakul', 'author_id': 8674380, 'author_profile': 'https://Stackoverflow.com/users/8674380', 'pm_score': 0, 'selected': False, 'text': "<pre><code>Public Function GetLastRow(ByVal SheetName As String) As Integer\n    Dim sht As Worksheet\n    Dim FirstUsedRow As Integer     'the first row of UsedRange\n    Dim UsedRows As Integer         ' number of rows used\n\n    Set sht = Sheets(SheetName)\n    ''UsedRange.Rows.Count for the empty sheet is 1\n    UsedRows = sht.UsedRange.Rows.Count\n    FirstUsedRow = sht.UsedRange.Row\n    GetLastRow = FirstUsedRow + UsedRows - 1\n\n    Set sht = Nothing\nEnd Function\n</code></pre>\n\n<p>sheet.UsedRange.Rows.Count:  retrurn number of rows used, not include empty row above the first row used</p>\n\n<p>if row 1 is empty, and the last used row is 10, UsedRange.Rows.Count will return 9, not 10.</p>\n\n<p>This function calculate the first row number of UsedRange plus number of UsedRange rows.</p>\n"}, {'answer_id': 49971492, 'author': 'Nickolay', 'author_id': 1026, 'author_profile': 'https://Stackoverflow.com/users/1026', 'pm_score': 2, 'selected': False, 'text': '<p>All the solutions relying on built-in behaviors (like <code>.Find</code> and <code>.End</code>) have limitations that are not well-documented (see <a href="https://stackoverflow.com/a/49971540/1026">my other answer</a> for details).</p>\n\n<p>I needed something that:</p>\n\n<ul>\n<li>Finds the last <strong>non-empty</strong> cell (i.e. that has <em>any formula or value</em>, even if it\'s an empty string) in a <strong>specific column</strong></li>\n<li>Relies on primitives with well-defined behavior</li>\n<li>Works reliably with autofilters and user modifications</li>\n<li>Runs as fast as possible on 10,000 rows (to be run in a <code>Worksheet_Change</code> handler without feeling sluggish)</li>\n<li>...with performance not falling off a cliff with accidental data or formatting put at the very end of the sheet (at ~1M rows)</li>\n</ul>\n\n<p>The solution below:</p>\n\n<ul>\n<li>Uses <code>UsedRange</code> 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);</li>\n<li>Goes backwards to find the row with data in the given column;</li>\n<li>...using VBA arrays to avoid accessing each row individually (in case there are many rows in the <code>UsedRange</code> we need to skip)</li>\n</ul>\n\n<p>(No tests, sorry)</p>\n\n<pre><code>\' 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)\nPrivate Function getLastNonblankRowInColumn(ws As Worksheet, colNo As Integer) As Long\n    \' Force Excel to recalculate the "last cell" (the one you land on after CTRL+END) / "used range"\n    \' and get the index of the row containing the "last cell". This is reasonably fast (~1 ms/10000 rows of a used range)\n    Dim lastRow As Long: lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row - 1 \' 0-based\n\n    \' Since the "last cell" is not necessarily the one we\'re looking for (it may be in a different column, have some\n    \' formatting applied but no value, etc), we loop backward from the last row towards the top of the sheet).\n    Dim wholeRng As Range: Set wholeRng = ws.Columns(colNo)\n\n    \' Since accessing cells one by one is slower than reading a block of cells into a VBA array and looping through the array,\n    \' we process in chunks of increasing size, starting with 1 cell and doubling the size on each iteration, until MAX_CHUNK_SIZE is reached.\n    \' In pathological cases where Excel thinks all the ~1M rows are in the used range, this will take around 100ms.\n    \' 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.\n    Const MAX_CHUNK_SIZE = 2 ^ 10 \' (using large chunks gives no performance advantage, but uses more memory)\n    Dim chunkSize As Long: chunkSize = 1\n    Dim startOffset As Long: startOffset = lastRow + 1 \' 0-based\n    Do \' Loop invariant: startOffset&gt;=0 and all rows after startOffset are blank (i.e. wholeRng.Rows(i+1) for i&gt;=startOffset)\n        startOffset = IIf(startOffset - chunkSize &gt;= 0, startOffset - chunkSize, 0)\n        \' Fill `vals(1 To chunkSize, 1 To 1)` with column\'s rows indexed `[startOffset+1 .. startOffset+chunkSize]` (1-based, inclusive)\n        Dim chunkRng As Range: Set chunkRng = wholeRng.Resize(chunkSize).Offset(startOffset)\n        Dim vals() As Variant\n        If chunkSize &gt; 1 Then\n            vals = chunkRng.Value2\n        Else \' reading a 1-cell range requires special handling &lt;http://www.cpearson.com/excel/ArraysAndRanges.aspx&gt;\n            ReDim vals(1 To 1, 1 To 1)\n            vals(1, 1) = chunkRng.Value2\n        End If\n\n        Dim i As Long\n        For i = UBound(vals, 1) To LBound(vals, 1) Step -1\n            If Not IsEmpty(vals(i, 1)) Then\n                getLastNonblankRowInColumn = startOffset + i\n                Exit Function\n            End If\n        Next i\n\n        If chunkSize &lt; MAX_CHUNK_SIZE Then chunkSize = chunkSize * 2\n    Loop While startOffset &gt; 0\n\n    getLastNonblankRowInColumn = 0\nEnd Function\n</code></pre>\n'}, {'answer_id': 55383256, 'author': 'Sumit Pokhrel', 'author_id': 2690723, 'author_profile': 'https://Stackoverflow.com/users/2690723', 'pm_score': 0, 'selected': False, 'text': '<pre><code>Last_Row = Range("A1").End(xlDown).Row\n</code></pre>\n\n<p>Just to verify, let\'s say you want to print the row number of the last row with the data in cell C1. </p>\n\n<pre><code>Range("C1").Select\nLast_Row = Range("A1").End(xlDown).Row\nActiveCell.FormulaR1C1 = Last_Row\n</code></pre>\n'}, {'answer_id': 71204877, 'author': 'Potocpe1', 'author_id': 8867339, 'author_profile': 'https://Stackoverflow.com/users/8867339', 'pm_score': 0, 'selected': False, 'text': '<p>get last non-empty row using <i>binary search</i></p>\n<ul>\n<li>returns correct value event though there are hidden values</li>\n<li>may returns incorrect value if there are empty cells before last non-empty cells (e.g. row 5 is empty, but row 10 is last non-empty row)</li>\n</ul>\n<pre><code>Function getLastRow(col As String, ws As Worksheet) As Long\n    Dim lastNonEmptyRow As Long\n    lastNonEmptyRow = 1\n    Dim lastEmptyRow As Long\n\n    lastEmptyRow = ws.Rows.Count + 1\n    Dim nextTestedRow As Long\n    \n    Do While (lastEmptyRow - lastNonEmptyRow &gt; 1)\n        nextTestedRow = Application.WorksheetFunction.Ceiling _\n            (lastNonEmptyRow + (lastEmptyRow - lastNonEmptyRow) / 2, 1)\n        If (IsEmpty(ws.Range(col &amp; nextTestedRow))) Then\n            lastEmptyRow = nextTestedRow\n        Else\n            lastNonEmptyRow = nextTestedRow\n        End If\n    Loop\n    \n    getLastRow = lastNonEmptyRow\n    \n\nEnd Function\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/71180', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/8418/']