How to find the max number of data filled cell in excel

[kcvinu]

Hi all,

I want to use _Excel_RangeRead function inside a loop, but how to know the maximum number of the cell  which contained data.So that i can start looping like this "For $i = 2 to Upper number"

[water]

Why use _Excel_RangeRead in a loop - that takes forever.

Use the function with default values and it will return an array with all used cells.

[computergroove]

The function page says that _Excel_RangeRead returns the data from the specified cell(s). A string for a cell, a zero-based array for a range of cells. Cant you use ubound with the returned array?

untested guess:

$data = _Excel_RangedRead($oWorkbook, Default, "A1")
For $i = 2 to ubound($data) - 1

[water]

According to the help file:

"$vRange [optional] Either a range object or an A1 range. If set to Default all used cells will be processed (default = keyword Default)"

If IsArray($data) Then
    If UBound($data, 0) = 2 Then ; 2D-array
        For $i = 0 To UBound($data, 1) - 1
            ; process first column of the returned 2D-array: $data[$i][0]
        Next
    Else
        For $i = 0 To UBound($data, 1) - 1
            ; process first column of the returned 2D-array: $data[$i]
        Next
    EndIf
EndIf

[kcvinu]

@water & @

computergroove, I have a workbook and it contains data in F2 to F22. Now, I need to read that data. But next time, my program has no idea about what is the upper limit. Now it is F22. But next time it will be F34 or F56 or something like that. Then how can i find it without a loop. And with this code, i only get one cell value. Not an array

$Result = _Excel_RangeRead($WBook, "Sheet2", "F2")

[kcvinu]

Oh my god.. i have found this from help file. 

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

[water]

As I stated above. Try:

$Result = _Excel_RangeRead($WBook, "Sheet2")

[kcvinu]

Yes.."$WBook.ActiveSheet.Usedrange.Columns" worked. 

[kcvinu]

@water, it will give all the data in that sheet. I think that will slow down my program a bit. Am i right ?

[water]

Only a bit. But $WBook.ActiveSheet.Usedrange.Columns is faster, no doubt.

[kcvinu]

@water, where i can get more details about these $oWorkBook.ActiveSheet. etc..? I mean i never saw any stuff related with dotted method like ActiveSheet.Usedrange 

[water]

Example: MSDN for Excel 2010.

[kcvinu]

@water, Thanks a lot.

Edited April 12, 2015 by kcvinu