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

User · Answer

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

User · Answer

Public Function LastData rCol As Range  As Range         Set LastData   rCol Find      rCol Cells 1         xlPrevious      End Function   Usage   lastdata activecell EntireColumn  Address

User · Answer

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     amp  Last 1  ActiveSheet Cells      MsgBox  The last column  R1C1  found is     amp  Last 2  ActiveSheet Cells      MsgBox  The last cell found is     amp  Last 3  ActiveSheet Cells      MsgBox  The last column  A1  found is     amp  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  gt  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   amp  Last  1          For i   1 To Len Last              s   Mid Last  i  1              If Not s Like     Then s1   s1  amp  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

User · Answer

How about   Function GetLastRow strSheet  strColumn  As Long     Dim MyRange As Range      Set MyRange   Worksheets strSheet  Range strColumn  amp   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

User · Answer

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

User · Answer

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 gt  0 and all rows after startOffset are blank  i e  wholeRng Rows i 1  for i gt  startOffset          startOffset   IIf startOffset - chunkSize  gt   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  gt  1 Then             vals   chunkRng Value2         Else   reading a 1-cell range requires special handling  lt http   www cpearson com excel ArraysAndRanges aspx gt              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  lt  MAX CHUNK SIZE Then chunkSize   chunkSize   2     Loop While startOffset  gt  0      getLastNonblankRowInColumn   0 End Function

User · Answer

Public Function LastData rCol As Range  As Range         Set LastData   rCol Find      rCol Cells 1         xlPrevious      End Function   Usage   lastdata activecell EntireColumn  Address

User · Answer

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  gt  iRowN Then iRowN   iRowI     Next     LastRow   iRowN End Function

User · Answer

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 gt  0 and all rows after startOffset are blank  i e  wholeRng Rows i 1  for i gt  startOffset          startOffset   IIf startOffset - chunkSize  gt   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  gt  1 Then             vals   chunkRng Value2         Else   reading a 1-cell range requires special handling  lt http   www cpearson com excel ArraysAndRanges aspx gt              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  lt  MAX CHUNK SIZE Then chunkSize   chunkSize   2     Loop While startOffset  gt  0      getLastNonblankRowInColumn   0 End Function

User · Answer

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

User · Answer

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

User · Answer

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  gt  iRowN Then iRowN   iRowI     Next     LastRow   iRowN End Function

User · Answer

How about   Function GetLastRow strSheet  strColumn  As Long     Dim MyRange As Range      Set MyRange   Worksheets strSheet  Range strColumn  amp   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

User · Answer

Public Function LastData rCol As Range  As Range         Set LastData   rCol Find      rCol Cells 1         xlPrevious      End Function   Usage   lastdata activecell EntireColumn  Address

User · Answer

How about   Function GetLastRow strSheet  strColumn  As Long     Dim MyRange As Range      Set MyRange   Worksheets strSheet  Range strColumn  amp   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

User · Answer

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

User · Answer

Public Function LastData rCol As Range  As Range         Set LastData   rCol Find      rCol Cells 1         xlPrevious      End Function   Usage   lastdata activecell EntireColumn  Address

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   amp  lastRow  Value    FIM  THE END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  iRowN Then iRowN   iRowI     Next     LastRow   iRowN End Function

User · Answer

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

User · Answer

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     amp  Last 1  ActiveSheet Cells      MsgBox  The last column  R1C1  found is     amp  Last 2  ActiveSheet Cells      MsgBox  The last cell found is     amp  Last 3  ActiveSheet Cells      MsgBox  The last column  A1  found is     amp  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  gt  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   amp  Last  1          For i   1 To Len Last              s   Mid Last  i  1              If Not s Like     Then s1   s1  amp  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

User · Answer

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  gt  iRowN Then iRowN   iRowI     Next     LastRow   iRowN End Function

User · Answer

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

User · Answer

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

User · Answer

How about   Function GetLastRow strSheet  strColumn  As Long     Dim MyRange As Range      Set MyRange   Worksheets strSheet  Range strColumn  amp   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   amp  lastRow  Value    FIM  THE END

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

Examples related to excel

Examples related to vba