Posted By

fabboi03 on 12/14/07


Tagged

excel


Versions (?)

returnLastUsedRow - Excel Function


 / Published in: Visual Basic
 

A basic function to return the last used row for a given row

  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
Posted By: neozhang on January 30, 2008

Why not use ActiveCell.Offset() to iterate the rows?

You need to login to post a comment.