# How can I find last row that contains data in a specific column?

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

``````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
``````
• 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 – GSerg Sep 16 '08 at 11:30
• @GSerg You can correct the code, then delete the comment if you like (since it won't apply any longer <g>) – JimmyPena Jun 13 '12 at 0:31
• I believe this will incorrectly report which cell is the last one if the cell had data and then the data was removed. – Scott Marcus Mar 13 '16 at 20:22
• 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 Apr 22 '18 at 23:48
• That `Cells.Find` function is what I have been needing for ever! Tired of determining the column, I want a range w/ top left/right and bottom used row anywhere! – FreeSoftwareServers Sep 30 at 3:50

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

• This appears to be a comment on @Fionnuala's answer. Also note that `xlUp` searches backwards from the end of a column, as if you pressed CTRL+UP, so it might produce incorrect results in presence of hidden rows (e.g. with autofilter ON). – Nickolay Jul 29 at 13:28

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"
``````
• Or like this 'function getSheetLastRow(sheet2Check as worksheet) lastRow = sheet2Check .Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row getSheetLastRow=lastRow end function' – user2988717 May 23 '16 at 19:20
• 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`... – Nickolay Apr 23 '18 at 0:02
``````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")
``````
• Is the check for isempty(r.value) really needed? Shouldn't it always have a value? – gdelfino Mar 26 '15 at 14:33
• @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. – GSerg Mar 26 '15 at 14:57
• 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). – Nickolay Apr 22 '18 at 23:57
• @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. – GSerg Apr 23 '18 at 0:23
• 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. – Nickolay Apr 23 '18 at 0:27
``````Public Function LastData(rCol As Range) As Range
Set LastData = rCol.Find("*", rCol.Cells(1), , , , xlPrevious)
End Function
``````

Usage: `?lastdata(activecell.EntireColumn).Address`

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
``````

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
If Err.Number > 0 Then
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
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
``````

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 Result in Immediate Window:

``````?Sheet1.UsedRange.Row+Sheet1.UsedRange.Rows.Count-1
21
``````
• Note `UsedRange` will also pick up formulas which may be a problem if you have formulae dragged down below visible data (or even formatting). – micstr Mar 17 '16 at 12:18
• @micstr True but so does every other method will do that. – Stupid_Intern Mar 25 '16 at 4:58
``````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.

• 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. – Nickolay Apr 23 '18 at 0:07
``````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
``````
``````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
``````
``````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"`.

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)
``````
• This doesn't work if there are any blank cells in the column. – Jon Crowell Jun 2 '12 at 23:13
{"ft_lang_label":"__label__en","ft_lang_prob":0.75446934,"math_prob":0.87073267,"size":15068,"snap":"2020-45-2020-50","text_gpt3_token_len":4239,"char_repetition_ratio":0.11238715,"word_repetition_ratio":0.06267806,"special_character_ratio":0.26659146,"punctuation_ratio":0.1453412,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9859635,"pos_list":[0,1,2],"im_url_duplicate_count":[null,10,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-11-29T13:42:58Z\",\"WARC-Record-ID\":\"<urn:uuid:9ce47692-9f95-4bc9-bca3-66fea7b79ea2>\",\"Content-Length\":\"287414\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:a4287797-0cf1-4bf8-8f91-08abc76f1b10>\",\"WARC-Concurrent-To\":\"<urn:uuid:86fbb3ab-e6be-48ed-9c89-ab956fb8d78c>\",\"WARC-IP-Address\":\"151.101.193.69\",\"WARC-Target-URI\":\"https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-a-specific-column\",\"WARC-Payload-Digest\":\"sha1:UMKO5PF6Y4QBXZQMOQUTIHNYPW3KPTK5\",\"WARC-Block-Digest\":\"sha1:L2ED4H6I7V5RHZSQRVGVQU7NNLBJ3M5B\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-50/CC-MAIN-2020-50_segments_1606141198409.43_warc_CC-MAIN-20201129123729-20201129153729-00096.warc.gz\"}"}