• Last row for Excel 07/10

    By David Turner 8 years ago
            The code which finds last row can be improved. The below approach sounds like it would probably be better for improved Excel &#39;07/10 compatibility. However I&#39;m not sure about it&#39;s performance, and I&#39;d also need to find the literal values behind the xlByRows etc constants for use in LotusScript.</p>
        <pre class="alt2" dir="ltr" style="margin: 0px;
        padding: 3px;
        border: 1px inset;
        width: 640px;
        height: 242px;
        text-align: left;
        overflow: auto">
        Sub FindLastRow()

    Dim LastRow As Long

    If WorksheetFunction.CountA(Cells) &gt; 0 Then
        &#39;Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:=&quot;*&quot;, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        MsgBox LastRow
    End If

    End Sub



  • Last Row implemented in v1.3

    By David Turner 8 years ago

    Hi All,

    I've just improved the LastRow function for release 1.3, to support spreadsheet data sets greater than 65536 rows, which is possible in Excel 2007 and Excel 2010.


    This page lists the Excel constants required by the Excel Cells.Find function: http://techsupt.winbatch.com/ts/T000001033005F9.html


    The resulting implementation is:

    'Excel constants
    Const xlUp% = -4162
    Const xlValues% = -4163
    Const xlWhole% = 1
    Const xlByRows% = 1
    Const xlNext% = 1
    Const xlPrevious% = 2


        Public Property Get LastDataRowNo(colNo As Long) As Long
            'Search for any entry searching backwards by Rows.
            'Definition: Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte)
            LastDataRowNo = varXLSheet.Cells.Find("*", Cell(1,colNo), _
                xlValues, xlWhole, xlByRows, xlPrevious, False).Row
        End Property


    Look for this enhancement in release 1.3 coming soon :)