Jump to content
Sign in to follow this  
CE101

Determining the Last Row in Excel

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.