Determining the Last Row in Excel

[CE101]

My script needs to manipulate some data in Excel.

In the Excel UDF I see the follow functions...

_ExcelReadCell($oExcel, $sRangeOrRow [, $iColumn = 1])

_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow [, $iColumn = 1])

Let's say I want to change all the cells in column C.

So the script needs to loop through column C, starting at row 1 until the last row.

The question is .... How will it know when it has reached the last row.

The fact that the cell contains a null value?

That doesn’t prove anything because the next cell may yet contain data.

Any suggestions would be greatly appreciated. Sample code would be even better.

Gary

[picaxe]

Const $xlByRows = 1
Const $xlByColumns = 2
Const $xlPrevious = 2

$oExcel = ObjCreate("Excel.Application")
With $oExcel ; open new workbook
    .Visible = True
    .WorkBooks.Add
    .ActiveWorkbook.Sheets(1).Select()
    $oSheet = .ActiveSheet
    $iStart = Random(1, 19, 1)
    $iEnd = Random(20, 49, 1)
    For $i = $iStart To $iEnd
        $oSheet.Cells($i, 1).Value = Random(1, 100000, 1)
    Next
    $iStart = Random(50, 69, 1)
    $iEnd = Random(70, 100, 1)
    For $i = $iStart To $iEnd
        $oSheet.Cells($i, 1).Value = Random(1, 100000, 1)
    Next
EndWith

$iLastRow = $oSheet.Cells.Find('*', $oSheet.Cells(1, 1), Default, Default, $xlByRows, $xlPrevious).Row
$oSheet.Cells(3, 3).Value = "Last non empty row = " & $iLastRow

[CE101]

Thank you.