Get the last row or column

Tested in Excel 365 (16.8730.2046) 64-bit

 
 
  1. Public Function LastRowOrCol(Optional IsColumn = False, Optional StartPos = 1, Optional NbIterations = 1)
  2.     Dim MaxRowCol, i
  3.    
  4.     MaxRowCol = 1
  5.     If IsColumn Then    ' get the last column
  6.        For i = StartPos To StartPos + NbIterations
  7.             If MaxRowCol < Cells(i, Columns.Count).End(xlToLeft).Column Then _
  8.                 MaxRowCol = Cells(i, Columns.Count).End(xlToLeft).Column
  9.         Next i
  10.     Else    ' get the last row
  11.        For i = StartPos To StartPos + NbIterations
  12.             If MaxRowCol < Cells(Rows.Count, i).End(xlUp).Row Then _
  13.                 MaxRowCol = Cells(Rows.Count, i).End(xlUp).Row
  14.         Next i
  15.     End If
  16.     LastRowOrCol = MaxRowCol
  17. End Function
  19. Sub example()
  21.     ' get the last row checking 7 rows starting from row 1
  22.    Debug.Print LastRowOrCol(, 1, 7)
  23.     ' get the last column checking 5 rows starting from column 1
  24.    Debug.Print LastRowOrCol(1, 2, 5)
  25. End Sub

 

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.