Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Find Next Empty Row

    I have a table on my worksheet that has UserID and SheetName

    those headers are on row C2 & D2

    When the workbook opens it will record the UserID and the SheetName into the first row which in this case would be C3, but what if C3 want the first empty row.

    How can I find the first empty row from C2, be it C3 or C33?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Jez,

    There are a few ways to get the last used row, some of which are faster or more reliable than others. This is my favourite function that I've written because it is very flexible (it can be used to find the last used row in a column, a portion of a worksheet or an entire worksheet):
    Code:
    Public Function GetLastRow(ByVal rngToCheck As Range) As Long
    
        Dim rngLast As Range
        
        Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)
        
        If rngLast Is Nothing Then
            GetLastRow = rngToCheck.Row
        Else
            GetLastRow = rngLast.Row
        End If
        
    End Function
    If you place that function in a standard code module you can then call it from the Workbook_Open() event handler, for example:
    Code:
    Private Sub Workbook_Open()
        Dim LastRow As Long
        
        'get the last used row in Sheet1!C:C
        LastRow = GetLastRow(Sheet1.Range("C:C"))
        
        'put a value in the next row down
        Sheet1.Cells(LastRow + 1, "C").Value = "new item " & (LastRow + 1)
        
    End Sub
    If you only ever need to find the last used row within a single column then a faster way is to use the Range.End(xlUp) property.

    Hope that helps...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •