Advanced Search

returnLastUsedRow - Excel Function

/ Published in: Visual Basic
Save to your folder(s)
A basic function to return the last used row for a given row

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Public Function returnLastUsedRow()
  2.  
  3.     Const col = 1 '1 for A 2 for B etc
  4.     Const lastrow = 65536
  5.  
  6.     Dim row As Integer
  7.     Dim count As Integer
  8.  
  9.     Dim CurrentCell As Range
  10.  
  11.  
  12.     Sheets("GR").Activate
  13.  
  14.     'Ask for starting row number
  15.     row = InputBox(Prompt:="Enter the starting row number.", Title:="Row Number", Default:="8")
  16.  
  17.     'Set currentCell
  18.     Set CurrentCell = Range(Cells(row, col), Cells(row, col))
  19.  
  20.     ' Check to ensure starting row isnt empty
  21.     If IsEmpty(CurrentCell) Then
  22.         MsgBox "Starting Row is empty"
  23.         Exit Function
  24.     End If
  25.  
  26.     ' Do until we hit last row in spreadsheet
  27.     Do While row <= lastrow
  28.  
  29.     ' If cell is empty get prevouis cell location and return it
  30.     ' else check next row
  31.         If IsEmpty(CurrentCell) Then
  32.             row = row - 1
  33.             Set CurrentCell = Range(Cells(row, col), Cells(row, col))
  34.             returnLastUsedRow = CurrentCell.Address
  35.             Exit Function
  36.  
  37.         Else
  38.             row = row + 1
  39.             Set CurrentCell = Range(Cells(row, col), Cells(row, col))
  40.         End If
  41.     Loop
  42.  
  43. End Function

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.