Error in finding last used cell in Excel with VBA

Question

When I want to find the last used cell value  I use  Dim LastRow As Long  LastRow   Range  quot E4 E48 quot   End xlDown  Row  Debug Print LastRow  I m 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

User · Answer

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 Shift + Ctrl + Enter to make it an array formula.

This will give you address of last used cell in the column D.

User · Answer

Sub lastRow        Dim i As Long         i   Cells Rows Count  1  End xlUp  Row             MsgBox i  End Sub  sub LastRow     Paste  amp  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    amp  i  End sub

User · Answer

Since the original question is about problems with finding the last cell  in this answer I will list the various ways you can get unexpected results  see my answer to  How can I find last row that contains data in the Excel sheet with a macro   for my take on solving this   I ll start by expanding on the answer by sancho s and the comment by GlennFromIowa  adding even more detail            one has first to decide what is considered used  I see at least 6 meanings  Cell has          1  data  i e   a formula  possibly resulting in a blank value    2  a value  i e   a non-blank formula or constant    3  formatting    4  conditional formatting    5  a shape  including Comment  overlapping the cell    6  involvement in a Table  List Object           Which combination do you want to test for  Some  such as Tables  may be more difficult to test for  and some may be rare  such as a shape outside of data range    but others may vary based on the situation  e g   formulas with blank values     Other things you might want to consider    A  Can there be hidden rows  e g  autofilter   blank cells or blank rows  B  What kind of performance is acceptable  C  Can the VBA macro affect the workbook or the application settings in any way    With that in mind  let s see how the common ways of getting the  last cell  can produce unexpected results    The  End xlDown  code from the question will break most easily  e g  with a single non-empty cell or when there are blank cells in between  for the reasons explained in the answer by Siddharth Rout here  search for  xlDown is equally unreliable     Any solution based on Counting  CountA or Cells  Count  or  CurrentRegion will also break in presence of blank cells or rows  A solution involving  End xlUp  to search backwards from the end of a column will  just as CTRL UP  look for data  formulas producing a blank value are considered  data   in visible rows  so using it with autofilter enabled might produce incorrect results      You have to take care to avoid the standard pitfalls  for details I ll again refer to the answer by Siddharth Rout here  look for the  Find Last Row in a Column  section   such as hard-coding the last row  Range  A65536   End xlUp   instead of relying on sht Rows Count   SpecialCells xlLastCell  is equivalent to CTRL END  returning the bottom-most and right-most cell of the  used range   so all caveats that apply to relying on the  used range   apply to this method as well  In addition  the  used range  is only reset when saving the workbook and when accessing worksheet UsedRange  so xlLastCell might produce stale results   with unsaved modifications  e g  after some rows were deleted   See the nearby answer by dotNET  sht UsedRange  described in detail in the answer by sancho s here  considers both data and formatting  though not conditional formatting  and resets the  used range  of the worksheet  which may or may not be what you want Note that a common mistake  is to use  UsedRange Rows Count    which returns the number of rows in the used range  not the last row number  they will be different if the first few rows are blank   for details see newguy s answer to How can I find last row that contains data in the Excel sheet with a macro   Find allows you to find the last row with any data  including formulas  or a non-blank value in any column  You can choose whether you re interested in formulas or values  but the catch is that it resets the defaults in the Excel s Find dialog       which can be highly confusing to your users  It also needs to be used carefully  see the answer by Siddharth Rout here  section  Find Last Row in a Sheet   More explicit solutions that check individual Cells  in a loop are generally slower than re-using an Excel function  although can still be performant   but let you specify exactly what you want to find  See my solution based on UsedRange and VBA arrays to find the last cell with data in the given column -- it handles hidden rows  filters  blanks  does not modify the Find defaults and is quite performant    Whatever solution you pick  be careful   to use Long instead of Integer to store the row numbers  to avoid getting Overflow with more than 65k rows  and to always specify the worksheet you re working with  i e  Dim ws As Worksheet     ws Range      instead of Range       when using  Value  which is a Variant  avoid implicit casts like  Value  lt  gt     as they will fail if the cell contains an error value

User · Answer

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

User · Answer

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

User · Answer

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  gt  0 Then             MsgBox  Cannot return reliable results  as there is at least one table in the worksheet               Exit Sub         End If          strResult    Workbook name     amp   Parent Name  amp  vbCrLf         strResult   strResult  amp   Sheet name     amp   Name  amp  vbCrLf           DATA           last data row         If Application WorksheetFunction CountA  Cells   lt  gt  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  amp   Last data row     amp  lngDataRow  amp  vbCrLf           last data column         If Application WorksheetFunction CountA  Cells   lt  gt  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  amp   Last data column     amp  lngDataCol  amp  vbCrLf           last data cell         strDataCell   Replace Cells lngDataRow  lngDataCol  Address       vbNullString          strResult   strResult  amp   Last data cell     amp  strDataCell  amp  vbCrLf           FORMATS           last data formatted grouped commented hidden row         strDataFormatRow   StrReverse Split StrReverse  UsedRange Address        0            strResult   strResult  amp   Last data formatted row     amp  strDataFormatRow  amp  vbCrLf           last data formatted grouped commented hidden column         lngDataFormatCol   Range StrReverse Split StrReverse  UsedRange Address        1    amp   1   Column          strResult   strResult  amp   Last data formatted column     amp  lngDataFormatCol  amp  vbCrLf           last data formatted grouped commented hidden cell         strDataFormatCell   Replace Cells strDataFormatRow  lngDataFormatCol  Address       vbNullString          strResult   strResult  amp   Last data formatted cell     amp  strDataFormatCell  amp  vbCrLf           CONDITIONAL FORMATS          For Each oFormatCond In  Cells FormatConditions               last conditionally-formatted row             lngTempRow   CLng StrReverse Split StrReverse oFormatCond AppliesTo Address        0                If lngTempRow  gt  lngCFRow Then lngCFRow   lngTempRow               last conditionally-formatted column             lngTempCol   Range StrReverse Split StrReverse oFormatCond AppliesTo Address        1    amp   1   Column             If lngTempCol  gt  lngCFCol Then lngCFCol   lngTempCol         Next          no results are returned for Conditional Format if there is no such         If lngCFRow  lt  gt  0 Then              strResult   strResult  amp   Last cond-formatted row     amp  lngCFRow  amp  vbCrLf              strResult   strResult  amp   Last cond-formatted column     amp  lngCFCol  amp  vbCrLf               last conditionally-formatted cell             strCFCell   Replace Cells lngCFRow  lngCFCol  Address       vbNullString              strResult   strResult  amp   Last cond-formatted cell     amp  strCFCell  amp  vbCrLf         End If           OVERALL          lngOverallRow   Application WorksheetFunction Max lngDataRow  strDataFormatRow  lngCFRow           strResult   strResult  amp   Last overall row     amp  lngOverallRow  amp  vbCrLf         lngOverallCol   Application WorksheetFunction Max lngDataCol  lngDataFormatCol  lngCFCol           strResult   strResult  amp   Last overall column     amp  lngOverallCol  amp  vbCrLf         strOverallCell   Replace  Cells lngOverallRow  lngOverallCol  Address       vbNullString          strResult   strResult  amp   Last overall cell     amp  strOverallCell  amp  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  gt  0 Then     MsgBox  Cannot return reliable results  as there is at least one table in the worksheet       Exit Sub End If

User · Answer

For the last 3  years these are the functions that I am using for finding last row and last column per defined column for row  and row for column    Last Column   Function lastCol Optional wsName As String  Optional rowToCheck As Long   1  As Long      Dim ws  As Worksheet      If wsName   vbNullString Then         Set ws   ActiveSheet     Else         Set ws   Worksheets wsName      End If      lastCol   ws Cells rowToCheck  ws Columns Count  End xlToLeft  Column  End Function   Last Row   Function lastRow Optional wsName As String  Optional columnToCheck As Long   1  As Long      Dim ws As Worksheet      If wsName   vbNullString Then         Set ws   ActiveSheet     Else         Set ws   Worksheets wsName      End If      lastRow   ws Cells ws Rows Count  columnToCheck  End xlUp  Row  End Function   For the case of the OP  this is the way to get the last row in column E   Debug Print lastRow columnToCheck  Range  E4 E48   Column   Last Row  counting empty rows with data   Here we may use the well-known Excel formulas  which give us the last row of a worksheet in Excel  without involving VBA -  IFERROR LOOKUP 2 1  NOT ISBLANK A A    ROW A A   0   In order to put this in VBA and not to write anything in Excel  using the parameters for the latter functions  something like this could be in mind   Public Function LastRowWithHidden Optional wsName As String  Optional columnToCheck As Long   1  As Long      Dim ws As Worksheet      If wsName   vbNullString Then         Set ws   ActiveSheet     Else         Set ws   Worksheets wsName      End If      Dim letters As String     letters   ColLettersGenerator columnToCheck      LastRowWithHidden   ws Evaluate   IFERROR LOOKUP 2 1  NOT ISBLANK    amp  letters  amp       ROW    amp  letters  amp       0     End Function  Function ColLettersGenerator col As Long  As String      Dim result As Variant     result   Split Cells 1  col  Address True  False            ColLettersGenerator   result 0   amp       amp  result 0   End Function

User · Answer

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  amp   -   amp  r Row End Sub

User · Answer

sub last filled cell   msgbox range  A65536   end xlup  row end sub   Here  A65536 is the last cell in the Column A this code was tested on excel 2003

User · Answer

Note  this answer was motivated by this comment  The purpose of UsedRange is different from what is mentioned in the answer above   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 three meanings    Used   non-blank  i e   having data  Used        in use  meaning the section that contains data or formatting   As per official documentation  this is the criterion used by Excel at the time of saving  See also this official documentation  If one is not aware of this  the criterion may produce 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  lest losing part of one s work  Used        in use  meaning the section that contains data or formatting  or conditional formatting  Same as 2   but also including cells that are the target for any Conditional Formatting rule    How to find the last used cell depends on what you want  your criterion    For criterion 1  I suggest reading this answer  Note that UsedRange is cited as unreliable  I think that is misleading  i e    unfair  to UsedRange   as UsedRange is simply not meant to report the last cell containing data  So it should not be used in this case  as indicated in that answer  See also this comment    For criterion 2  UsedRange is the most reliable option  as compared to other options also designed for this use  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    For criterion 3  I do not know any built-in method  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  Accounting for this would require some VBA programming       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

User · Answer

Here s my two cents  IMHO the risk of a hidden row with data being excluded is too significant to let xlUp be considered a One stop answer  I agree it s simple and will work MOST of the time  but it presents the risk of understating the last row  without any warning  This could produce CATASTROPHIC results at some poinit for someone who jumped on Stack Overlow and was looking to  quot sure way quot  to capture this value  The Find method is flawless with respect to reliably pulling the last non-blank row and it would be my One Stop Answer  However the drawback of changing the Find settings can be annoying  particularly if this is part of a UDF  The other answers posted are okay  however the complexity gets a little excessive  Thus here s my attempt to find a balance of reliability  minimal complexity  and not using Find  Function LastRowNumber Optional rng As Range  As Long  If rng Is Nothing Then     Set rng   ActiveSheet UsedRange Else     Set rng   Intersect rng Parent UsedRange  rng EntireColumn      If rng Is Nothing Then         LastRowNumber   1         Exit Function     ElseIf isE   0 Then         LastRowNumber   1         Exit Function          End If  End If  LastRowNumber   rng Cells rng Rows Count  1  Row  Do While IsEmpty Intersect rng        rng Parent Rows LastRowNumber         LastRowNumber   LastRowNumber - 1 Loop  End Function  Why this is good   Reasonably simple  not a lot of variables  Allows for multiple columns  Doesn t modify Find settings Dynamic if used as a UDF with the entire column selected   Why this is bad   With very large sets of data and a massive gap between used range and last row in specified columns  this will perform slower  in rare cases SIGNIFICANTLY slower   However  I think a One-Stop-Solution that has a drawback of messing up find settings or performing slower is a better overall solution  A user can then tinker with their settings to try to improve  knowing what s going on with their code  Using xLUp will not warn of the potential risks and they could carry on for who knows how long not knowing their code was not working correctly

User · Answer

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    UsedRange xlDown 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   amp   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   Note that this approach will skip any hidden rows  Looking back at my screenshot above for column A  if row 8 were hidden  this approach would return 5 instead of 8     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   lt  gt  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

User · Answer

Find Last Row in a Column OR a Table Column ListObject  by range Finding the last row requires   Specifying several parameters  table name  column inside table relative to first column  worksheet  range   May require switching between methods  e g  if the range is a Table  List Object  or not  Using the wrong search type will return wrong results   This proposed solution is more general   requires only the range  less chance of typos and is short  just calling MyLastRow function    Sub test   Dim rng As Range Dim Result As Long Set rng   Worksheets 1  Range  quot D4 quot   Result   MyLastRow rng  End Sub       Function MyLastRow FirstRow As Range  As Long     Dim WS As Worksheet     Dim TableName As String     Dim ColNumber As Long     Dim LastRow As Long     Dim FirstColumnTable As Long     Dim ColNumberTable As Long     Set WS   FirstRow Worksheet     TableName   GetTableName FirstRow      ColNumber   FirstRow Column            If the table  ListObject  does not start in column  quot A quot  we need to calculate the        first Column table and how many Columns from its beginning the Column is located      If TableName  lt  gt  vbNullString Then      FirstColumnTable   WS ListObjects TableName  ListColumns 1  Range Column      ColNumberTable   ColNumber - FirstColumnTable   1     End If       If TableName   vbNullString Then     LastRow   WS Cells WS Rows Count  ColNumber  End xlUp  Row     Else     LastRow   WS ListObjects TableName  ListColumns ColNumberTable  Range Find                   What   quot   quot   SearchOrder  xlByRows  SearchDirection  xlPrevious  Row     End If     MyLastRow   LastRow     End Function            Get Table Name by Cell Range     Function GetTableName CellRange As Range  As String         If CellRange ListObject Is Nothing Then             GetTableName   vbNullString         Else             GetTableName   CellRange ListObject Name         End If     End Function

User · Answer

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

[excel] Error in finding last used cell in Excel with VBA

Examples related to excel

Examples related to vba

Examples related to excel-formula