Home Error in finding last used cell in VBA

# Error in finding last used cell in VBA

james
1#
james Published in 2012-06-23 12:20:51Z
 When I want to find the last used cell value, I use: Dim last_row As Integer Dim LastRow As Long LastRow = Range("E4:E48").End(xlDown).Row Debug.Print LastRow  I am getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. What's the reason behind this?
Community
2#

NOTE: I intend to make this a "one stop post" where you can use the Correct way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.

## Unreliable ways of finding the last row

Some of the most common ways of finding last row which are highly unreliable and hence should never be used.

1. UsedRange
2. xlDown
3. CountA

UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment.

Type something in cell A5. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell A10 red. If you now use the any of the below code, you will still get 5. If you use Usedrange.Rows.Count what do you get? It won't be 5.

Here is a scenario to show how UsedRange works.

xlDown is equally unreliable.

Consider this code

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


What would happen if there was only one cell (A1) which had data? You will end up reaching the last row in the worksheet! It's like selecting cell A1 and then pressing End key and then pressing Down Arrow key. This will also give you unreliable results if there are blank cells in a range.

CountA is also unreliable because it will give you incorrect result if there are blank cells in between.

And hence one should avoid the use of UsedRange, xlDown and CountA to find the last cell.

## Find Last Row in a Column

To find the last Row in Col E use this

With Sheets("Sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With


If you notice that we have a . before Rows.Count. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using . before Rows.Count and Columns.Count. That question is a classic scenario where the code will fail because the Rows.Count returns 65536 for Excel 2003 and earlier and 1048576 for Excel 2007 and later. Similarly Columns.Count returns 256 and 16384, respectively.

The above fact that Excel 2007+ has 1048576 rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long instead of Integer else you will get an Overflow error.

## Find Last Row in a Sheet

To find the Effective last row in the sheet, use this. Notice the use of Application.WorksheetFunction.CountA(.Cells). This is required because if there are no cells with data in the worksheet then .Find will give you Run Time Error 91: Object Variable or With block variable not set

With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
End With


## Find Last Row in a Table (ListObject)

The same principles apply, for example to get the last row in the third column of a table:

Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1")  'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")

With tbl.ListColumns(3).Range
lastrow = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With

End Sub

Community
3#
 As to the correct way of finding the last used cell, one has first to decide what is considered used, and then select a suitable method. I conceive at least two meanings: Used = non-blank, i.e., having data. Used = "... in use, meaning the section that contains data or formatting." This is the criterion used by Excel at the time of saving. See also this. This criterion usually produces unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook. How to find the last used cell depends on what you want (your criterion). For criterion 1, I suggest reading this answer. For criterion 2, UsedRange is the most reliable option. It even makes it unnecessary to save a workbook to make sure that the last cell is updated. Ctrl+End will go to a wrong cell prior to saving (“The last cell is not reset until you save the worksheet”, from http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx. It is an old reference, but in this respect valid). There is yet another pitfall: Criterion 2 does not account for Conditional Formatting. One may have formatted cells, based on formulas, which are not detected by UsedRange or Ctrl+End. In the figure, the last cell is B3, since formatting was applied explicitly to it. Cells B6:D7 have a format derived from a Conditional Formatting rule, and this is not detected even by UsedRange. As to your specific question: What's the reason behind this? Your code uses the first cell in your range E4:E48 as a trampoline, for jumping down with End(xlDown). The "erroneous" output will obtain if there are no non-blank cells in your range other than perhaps the first. Then, you are leaping in the dark, i.e., down the worksheet (you should note the difference between blank and empty string!). Note that: If your range contains non-contiguous non-blank cells, then it will also give a wrong result. If there is only one non-blank cell, but it is not the first one, your code will still give you the correct result.
ZygD
4#
 I created this one-stop function for determining the last row, column and cell, be it for data, formatted (grouped/commented/hidden) cells or conditional formatting. Sub LastCellMsg() Dim strResult As String Dim lngDataRow As Long Dim lngDataCol As Long Dim strDataCell As String Dim strDataFormatRow As String Dim lngDataFormatCol As Long Dim strDataFormatCell As String Dim oFormatCond As FormatCondition Dim lngTempRow As Long Dim lngTempCol As Long Dim lngCFRow As Long Dim lngCFCol As Long Dim strCFCell As String Dim lngOverallRow As Long Dim lngOverallCol As Long Dim strOverallCell As String With ActiveSheet If .ListObjects.Count > 0 Then MsgBox "Cannot return reliable results, as there is at least one table in the worksheet." Exit Sub End If strResult = "Workbook name: " & .Parent.Name & vbCrLf strResult = strResult & "Sheet name: " & .Name & vbCrLf 'DATA: 'last data row If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lngDataRow = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Else lngDataRow = 1 End If 'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf 'last data column If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lngDataCol = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else lngDataCol = 1 End If 'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf 'last data cell strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString) strResult = strResult & "Last data cell: " & strDataCell & vbCrLf 'FORMATS: 'last data/formatted/grouped/commented/hidden row strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0)) 'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf 'last data/formatted/grouped/commented/hidden column lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column 'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf 'last data/formatted/grouped/commented/hidden cell strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString) strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf 'CONDITIONAL FORMATS: For Each oFormatCond In .Cells.FormatConditions 'last conditionally-formatted row lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0))) If lngTempRow > lngCFRow Then lngCFRow = lngTempRow 'last conditionally-formatted column lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column If lngTempCol > lngCFCol Then lngCFCol = lngTempCol Next 'no results are returned for Conditional Format if there is no such If lngCFRow <> 0 Then 'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf 'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf 'last conditionally-formatted cell strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString) strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf End If 'OVERALL: lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow) 'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol) 'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString) strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf MsgBox strResult Debug.Print strResult End With End Sub  Results look like this: For more detailed results, some lines in the code can be uncommented: One limitation exists - if there are tables in the sheet, results can become unreliable, so I decided to avoid running the code in this case: If .ListObjects.Count > 0 Then MsgBox "Cannot return reliable results, as there is at least one table in the worksheet." Exit Sub End If 
no comprende
5#
no comprende Reply to 2014-11-05 15:24:15Z
 I would add to the answer given by Siddarth Rout to say that the CountA call can be skipped by having Find return a Range object, instead of a row number, and then test the returned Range object to see if it is Nothing (blank worksheet). Also, I would have my version of any LastRow procedure return a zero for a blank worksheet, then I can know it is blank.
ZygD
6#
 One important note to keep in mind when using the solution ... LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row  ... is to ensure that your LastRow variable is of Long type: Dim LastRow as Long  Otherwise you will end up getting OVERFLOW errors in certain situations in .XLSX workbooks This is my encapsulated function that I drop in to various code uses. Private Function FindLastRow(ws As Worksheet) As Long ' -------------------------------------------------------------------------------- ' Find the last used Row on a Worksheet ' -------------------------------------------------------------------------------- If WorksheetFunction.CountA(ws.Cells) > 0 Then ' Search for any entry, by searching backwards by Rows. FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row End If End Function 
dotNET
7#
 I wonder that nobody has mentioned this, But the easiest way of getting the last used cell is: Function GetLastCell(sh as Worksheet) As Range GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell) End Function  This essentially returns the same cell that you get by Ctrl + End after selecting Cell A1. A word of caution: Excel keeps track of the most bottom-right cell that was ever used in a worksheet. So if for example you enter something in B3 and something else in H8 and then later on delete the contents of H8, pressing Ctrl + End will still take you to H8 cell. The above function will have the same behavior.
Ashwith Ullal
8#
Ashwith Ullal Reply to 2015-12-13 04:49:24Z
 sub last_filled_cell() msgbox range("a65536").end(xlup).row end sub  "here a65536 is last cell in the column a this code was tested on excel sti72003"200 and if u are using its "a1,048,576" my code is just for the beginners to understand the concepts of what end(xlup) and other related commands can do
Vasfed
9#
 Sub lastRow() Dim i As Long i = Cells(Rows.Count, 1).End(xlUp).Row MsgBox i End Sub sub LastRow() 'Paste & for better understanding of the working use F8 Key to run the code . dim WS as worksheet dim i as long set ws = thisworkbook("SheetName") ws.activate ws.range("a1").select ws.range("a1048576").select activecell.end(xlup).select i= activecell.row msgbox "My Last Row Is " & i End sub 
Masoud
10#
 However this question is seeking to find the last row using VBA, I think it would be good to include an array formula for worksheet function as this gets visited frequently: {=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}  You need to enter the formula without brackets and then hit Shitf + Ctrl + Enter to make it an array formula. This will give you address of last used cell in the column D.
J. Chomel
11#
J. Chomel Reply to 2017-05-17 15:23:25Z
 I was looking for a way to mimic the CTRL+Shift+End, so dotNET solution is great, except with my Excel 2010 I need to add a set if I want to avoid an error: Function GetLastCell(sh As Worksheet) As Range Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell) End Function  and how to check this for yourself: Sub test() Dim ws As Worksheet, r As Range Set ws = ActiveWorkbook.Sheets("Sheet1") Set r = GetLastCell(ws) MsgBox r.Column & "-" & r.Row End Sub 
 You need to login account before you can post.
Processed in 0.535488 second(s) , Gzip On .