Function GetNextCell(lColNum As Long, _
    oSh As Worksheet, _
    Optional bWholeSheet As Boolean = False) As Range
    ‘Returns the next available cell for a column
    ‘lColNum = column to return
    ‘oSh = Parent of returned range
    ‘bWholeSheet = True if next row is based on all columns,
    ‘     False if next row is based on specified column only
    Dim rNext As Range
    If bWholeSheet Then
        On Error Resume Next
            Set rNext = oSh.Cells.Find( _
                what:=”*”, _
                after:=oSh.Cells(1, 1), _
                searchdirection:=xlPrevious).Offset(1, 0)
        On Error GoTo 0
        If rNext Is Nothing Then
            Set rNext = oSh.Range(“A1?)
        End If
        Set rNext = oSh.Cells(oSh.Rows.Count, lColNum).End(xlUp).Offset(1, 0)
        If rNext.Row = 2 And IsEmpty(rNext.Offset(-1, 0)) Then
            Set rNext = oSh.Cells(1, lColNum)
        End If
    End If
    Set GetNextCell = oSh.Cells(rNext.Row, lColNum)
End Function

Posted in Uncategorized

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.