Find last used row in an Excel table (ignore cell formatting)

[cnilsson76]

I have been successfully finding the last used row of Excel sheets with the following line.

$oRange = $oWorkbook.Sheets("Raw Data").UsedRange.SpecialCells($xlCellTypeLastCell)

This works perfectly if there is no formatting in the sheet. However, if there is an empty cell with any formatting (highlighting, borders, defined number format, etc.) then that is the returned range. This is the correct behavior of the .UsedRange property and is expected. However, I need to find the last row containing data without regard to any formatting. The sheet I am searching has a defined Excel table object covering A:K for the Max length Excel will allow (=$A$2:$K$1048471).

This means that all the cells in the table have been "touched" by a user/script and thus the Used.Range function does not return the last row with actual data.

I found this post on stackoverflow which includes a great answer for VBA users.

In has a section for "Find Last Row in a Sheet"

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With

I have tried to use the _Excel_RangeFind function with the values for LookAt, LookIn, etc. from the VBA above plugged in but every permutation generates an error. Just one failed example is below.

Local $oRange = _Excel_RangeFind($oWorkbook.Sheets("Raw Data"), "*", .Range("A1"))

The stackoverflow post also has a section called  "Find Last Row in a Table (ListObject)" which is exactly what I need to do, however, I have no idea how to translate this into AutoIT.

Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1")  'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")

With tbl.ListColumns(3).Range
    lastrow = .Find(What:="*", _
                After:=.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
End With

End Sub

I have also seen methods that talk about reading up from the bottom until you reach some data but I'm concerned about performance with the table range being so large.

Are there any sure fire ways to find the last used row (used meaning not blank) in an Excel table while ignoring all formatting?

Edited January 31, 2022 by cnilsson76

[water]

Maybe something like this? 

#include <Excel.au3>

; Pass the following parameters:
;  * Object of the Workbook
;  * Number or Name of the Worksheet
;  * Name or Index number of the table
;  * Name or Index number of the Column
Func FindLastRowInExcelTable($oWorkbook, $vWorksheet, $sTable, $iColumn)
    Local $xlByRows = 1 ; Searches across a row, then moves to the next row.
    Local $xlPrevious = 2 ; Search for previous matching value in range.
    Local $iLastRow, $oWorksheet, $oTable
    $oWorksheet = $oWorkbook.Sheets($vWorksheet)
    $oTable = $oWorksheet.ListObjects($sTable)
    With $oTable.ListColumns($iColumn).Range
        $iLastRow = .Find("*", .Cells(1), $xlFormulas, $xlPart, $xlByRows, $xlPrevious, False).Row
    EndWith
EndFunc   ;==>FindLastRowInExcelTable

 

[water]

BTW: 
More information can be found in the wiki: Excel Range - AutoIt Wiki (autoitscript.com) 

[cnilsson76]

On 1/31/2022 at 2:40 AM, water said:

Maybe something like this? 

#include <Excel.au3>

; Pass the following parameters:
;  * Object of the Workbook
;  * Number or Name of the Worksheet
;  * Name or Index number of the table
;  * Name or Index number of the Column
Func FindLastRowInExcelTable($oWorkbook, $vWorksheet, $sTable, $iColumn)
    Local $xlByRows = 1 ; Searches across a row, then moves to the next row.
    Local $xlPrevious = 2 ; Search for previous matching value in range.
    Local $iLastRow, $oWorksheet, $oTable
    $oWorksheet = $oWorkbook.Sheets($vWorksheet)
    $oTable = $oWorksheet.ListObjects($sTable)
    With $oTable.ListColumns($iColumn).Range
        $iLastRow = .Find("*", .Cells(1), $xlFormulas, $xlPart, $xlByRows, $xlPrevious, False).Row
    EndWith
EndFunc   ;==>FindLastRowInExcelTable

 

This is perfect! The syntax for how to reference a named table and column was the missing part for me. 

$oTable = $oWorksheet.ListObjects($sTable)
With $oTable.ListColumns($iColumn).Range
    $iLastRow = .Find("*", .Cells(1), $xlFormulas, $xlPart, $xlByRows, $xlPrevious, False).Row
EndWith

I had poured over the docs but was struggling to merge Excels dot notation with AutoIT. Thank you for your help!

[water]

😃