Better way to find last used row

Question

I am trying to find the last row the same way I found the last column  Sheets  quot Sheet2 quot   Cells 1 Sheets  quot Sheet2 quot   Columns Count  End xlToLeft  Column  I know this way but it is not as helpful as the prior would be  u   Sheets  quot Sheet1 quot   Range  quot A65536 quot   End xlUp  Row  I tried  Sheets  quot Sheet2 quot   Cells Sheets  quot Sheet2 quot  1  Rowa Count  End xlToUP  Column  Synopsis  I would like the below way for last row  Sheets  quot Sheet2 quot   Cells 1 Sheets  quot Sheet2 quot   Columns Count  End xlToLeft  Column

User · Answer

How is this   dim rownum as integer dim colnum as integer dim lstrow as integer dim lstcol as integer dim r as range   finds the last row  lastrow   ActiveSheet UsedRange Rows Count   finds the last column  lastcol   ActiveSheet UsedRange Columns Count   sets the range  set r   range cells rownum colnum   cells lstrow lstcol

User · Answer

You should use a with statement to qualify both your Rows and Columns counts   This will prevent any errors while working with older pre 2007 and newer 2007 Excel Workbooks   Last Column  With Sheets  Sheet2        Cells 1   Columns Count  End xlToLeft  Column End With    Last Row  With Sheets  Sheet2        Range  A   amp   Rows Count  End xlUp  Row End With    Or   With Sheets  Sheet2        Cells  Rows Count  1  End xlUp  Row End With

User · Answer

I preferred search last blank cell  Il you want last empty cell of column you can do that Dim sh as Worksheet  r as range set sh   ActiveWorksheet  if you want an other it s possible   find a value   Columns  quot A D quot    to check on multiple columns  Set r   sh Columns  quot A quot   Find What   quot   quot   SearchOrder  xlByRows  SearchDirection  xlPrevious   no value return first row If r Is Nothing Then Set r   sh Cells 1   quot A quot   Else Set r   sh1 Cells r Row   1   quot A quot    If this is to insert new row  find on multiple columns is a good choice because first column can contains less rows than next columns

User · Answer

This is the best way I ve seen to find the last cell   MsgBox ActiveSheet UsedRage SpecialCells xlCellTypeLastCell  Row   One of the disadvantages to using this is that it s not always accurate  If you use it then delete the last few rows and use it again  it does not always update  Saving your workbook before using this seems to force it to update though   Using the next bit of code after updating the table  or refreshing the query that feeds the table  forces everything to update before finding the last row  But  it s been reported that it makes excel crash  Either way  calling this before trying to find the last row will ensure the table has finished updating first    Application CalculateUntilAsyncQueriesDone   Another way to get the last row for any given column  if you don t mind the overhead   Function GetLastRow col  row        col and row are where we will start        We will find the last row for the given column      Do Until ActiveSheet Cells row  col               row   row   1     Loop     GetLastRow   row End Function

User · Answer

This gives you last used row in a specified column    Optionally you can specify worksheet  else it will takes active sheet   Function shtRowCount colm As Integer  Optional ws As Worksheet  As Long      If ws Is Nothing Then Set ws   ActiveSheet      If ws Cells Rows Count  colm   lt  gt     Then         shtRowCount   ws Cells Rows Count  colm  Row         Exit Function     End If      shtRowCount   ws Cells Rows Count  colm  Row      If shtRowCount   1 Then         If ws Cells 1  colm       Then             shtRowCount   0         Else             shtRowCount   1         End If     End If  End Function  Sub test        Dim lgLastRow As Long     lgLastRow   shtRowCount 2   Column B  End Sub

User · Answer

This function should do the trick if you want to specify a particular sheet  I took the solution from user6432984 and modified it to not throw any errors  I am using Excel 2016 so it may not work for older versions   Function findLastRow ByVal inputSheet As Worksheet  As Integer     findLastRow   inputSheet cellS inputSheet Rows Count  1  End xlUp  Row End Function   This is the code to run if you are already working in the sheet you want to find the last row of   Dim lastRow as Integer lastRow   cellS Rows Count  1  End xlUp  Row

User · Answer

I use this routine to find the count of data rows   There is a minimum of overhead required  but by counting using a decreasing scale  even a very large result requires few iterations   For example  a result of 28 395 would only require 2   8   3   9   5  or 27 times through the loop  instead of a time-expensive 28 395 times   Even were we to multiply that by 10  283 950   the iteration count is the same 27 times   Dim lWorksheetRecordCountScaler as Long Dim lWorksheetRecordCount as Long  Const sDataColumn    A      lt ----Set to column that has data in all rows  Code  ID  etc         Count the data records     lWorksheetRecordCountScaler   100000   Begin by counting in 100 000-record bites     lWorksheetRecordCount   lWorksheetRecordCountScaler      While lWorksheetRecordCountScaler  gt   1          While Sheets  Sheet2   Range sDataColumn  amp  lWorksheetRecordCount   2  Formula  gt                  lWorksheetRecordCount   lWorksheetRecordCount   lWorksheetRecordCountScaler         Wend           To the beginning of the previous bite  count 1 10th of the scale from there         lWorksheetRecordCount   lWorksheetRecordCount - lWorksheetRecordCountScaler         lWorksheetRecordCountScaler   lWorksheetRecordCountScaler   10      Wend      lWorksheetRecordCount   lWorksheetRecordCount   1    Final answer

User · Answer

I use the following function extensively   As pointed out above  using other methods can sometimes give inaccurate results due to used range updates  gaps in the data  or different columns having different row counts  Example of use  lastRow FindRange  quot Sheet1 quot   quot A1 A1000 quot    would return the last occupied row number of the entire range   You can specify any range you want from single columns to random rows  eg FindRange  quot Sheet1 quot   quot A100 A150 quot   Public Function FindRange inSheet As String  inRange As String  As Long     Set fr   ThisWorkbook Sheets inSheet  Range inRange  find  quot   quot   SearchOrder  xlByRows  SearchDirection  xlPrevious      If Not fr Is Nothing Then FindRange   fr row Else FindRange   0 End Function

[excel] Better way to find last used row

Examples related to excel

Examples related to vba