Posted By

fabboi03 on 12/14/07

Tagged

excel

Versions (?)

Last Edited at 12/14/07 06:01am

Statistics

Viewed 591 times

Favorited by 0 user(s)

Related snippets

returnLastUsedRow - Excel Function

/ Published in: Visual Basic

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

Highlighted TextExpand

Public Function returnLastUsedRow()

Const col = 1 '1 for A 2 for B etc
    Const lastrow = 65536
      
    Dim row As Integer
    Dim count As Integer
    
    Dim CurrentCell As Range

Sheets("GR").Activate
    
    'Ask for starting row number
    row = InputBox(Prompt:="Enter the starting row number.", Title:="Row Number", Default:="8")
    
    'Set currentCell
    Set CurrentCell = Range(Cells(row, col), Cells(row, col))
    
    ' Check to ensure starting row isnt empty
    If IsEmpty(CurrentCell) Then
        MsgBox "Starting Row is empty"
        Exit Function
    End If
    
    ' Do until we hit last row in spreadsheet
    Do While row <= lastrow
    
    ' If cell is empty get prevouis cell location and return it
    ' else check next row
        If IsEmpty(CurrentCell) Then
            row = row - 1
            Set CurrentCell = Range(Cells(row, col), Cells(row, col))
            returnLastUsedRow = CurrentCell.Address
            Exit Function
            
        Else
            row = row + 1
            Set CurrentCell = Range(Cells(row, col), Cells(row, col))
        End If
    Loop
        
End Function

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.