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

Question

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

Possible duplicate of Error in finding last used cell in VBA — M--
– M--, Commented Jul 7, 2017 at 14:23
The Error in finding last used cell in VBA question mentioned by Siddharth and Masoud above is not the same, though the answers to that question are very relevant to this question -- I tried to sum up the answers here. The Last not empty cell (column) in the given row; Excel VBA question referenced by brettdj is about finding the last column, the solutions to which rely on similar ideas, but require different code. — Nickolay
– Nickolay, Commented Apr 22, 2018 at 23:42

MarredCheese · Accepted Answer · 2019-05-30 01:02:19Z

47

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

edited May 30, 2019 at 1:02

MarredCheese

21.3k12 gold badges109 silver badges105 bronze badges

answered Sep 16, 2008 at 11:16

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GSerg Over a year ago

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 Over a year ago

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

Scott Marcus Over a year ago

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

Nickolay Over a year ago

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).

FreeSoftwareServers Over a year ago

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!

Siddharth Rout · Accepted Answer · 2012-06-02 16:17:31Z

21

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

edited Jun 2, 2012 at 16:17

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Sep 16, 2008 at 15:21

Jon Fournier

4,3373 gold badges38 silver badges44 bronze badges

1 Comment

Nickolay Over a year ago

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).

dwitvliet · Accepted Answer · 2014-08-26 15:32:42Z

9

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"

edited Aug 26, 2014 at 15:32

dwitvliet

7,7317 gold badges40 silver badges66 bronze badges

answered Aug 26, 2014 at 15:14

user2988717

1273 silver badges8 bronze badges

2 Comments

user2988717 Over a year ago

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

Nickolay Over a year ago

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

GSerg · Accepted Answer · 2008-09-16 11:14:22Z

8

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")

answered Sep 16, 2008 at 11:14

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

7 Comments

gdelfino Over a year ago

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

GSerg Over a year ago

@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 Over a year ago

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 Over a year ago

@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 Over a year ago

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.

|

brettdj · Accepted Answer · 2013-12-19 09:40:56Z

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

Usage: ?lastdata(activecell.EntireColumn).Address

Nickolay · Accepted Answer · 2018-04-22 23:02:25Z

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

This technique wouldn't give an incorrect answer ever, and is the best one of the lot! I prefer the XML way myself

2 revs, 2 users 67% Greg Podesta · Accepted Answer · 2011-11-18 20:57:40Z

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

Phaithoon Jariyanantakul · Accepted Answer · 2017-09-26 06:21:50Z

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.

MarredCheese · Accepted Answer · 2019-05-30 01:05:07Z

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

Potocpe1 · Accepted Answer · 2022-02-21 10:41:18Z

get last non-empty row using binary search

returns correct value event though there are hidden values
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)

Function getLastRow(col As String, ws As Worksheet) As Long Dim lastNonEmptyRow As Long lastNonEmptyRow = 1 Dim lastEmptyRow As Long lastEmptyRow = ws.Rows.Count + 1 Dim nextTestedRow As Long Do While (lastEmptyRow - lastNonEmptyRow > 1) nextTestedRow = Application.WorksheetFunction.Ceiling _ (lastNonEmptyRow + (lastEmptyRow - lastNonEmptyRow) / 2, 1) If (IsEmpty(ws.Range(col & nextTestedRow))) Then lastEmptyRow = nextTestedRow Else lastNonEmptyRow = nextTestedRow End If Loop getLastRow = lastNonEmptyRow End Function

Community · Accepted Answer · 2017-05-23 12:10:44Z

-1

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

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Mar 13, 2016 at 20:16

Stupid_Intern

3,4808 gold badges42 silver badges78 bronze badges

2 Comments

micstr Over a year ago

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

Stupid_Intern Over a year ago

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

Galwegian · Accepted Answer · 2008-09-16 10:56:52Z

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

MarredCheese · Accepted Answer · 2019-05-30 01:06:17Z

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".

MarredCheese · Accepted Answer · 2019-05-30 01:08:47Z

-2

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)

edited May 30, 2019 at 1:08

MarredCheese

21.3k12 gold badges109 silver badges105 bronze badges

answered Sep 16, 2008 at 11:23

databyss

6,5281 gold badge23 silver badges24 bronze badges

1 Comment

Jon Crowell Over a year ago

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

Collectives™ on Stack Overflow

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

14 Answers 14

5 Comments

1 Comment

2 Comments

7 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

5 Comments

1 Comment

2 Comments

7 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

1 Comment

Linked

Related