Advanced Search

Return to Snippet

Revision: 4454
at December 14, 2007 06:01 by fabboi03
Initial Code 
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
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

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

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
returnLastUsedRow - Excel Function

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
excel

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
Visual Basic