Excel count rows

[motormad]

I use

$iRows = $oExcel.ActiveSheet.UsedRange.Rows.Count

to count the rows in this excel.

then i import the excel to an array

$aWO_Nr = _ExcelReadSheetToArray($oExcel,1,1)

how come thet it counts 65000+ rows ?

Therefore the import to the array doesnt work.

Map1.zip

[xeroTechnologiesLLC]

The range needs to be specified. Whenever I tried any other method it returned an obscene number as you also noted.

Hope this helps.

$iRows = $oExcel.Range("startingrow:endingrow").Rows.Count

edit: made some grammatical corrections

Edited August 5, 2012 by xeroTechnologiesLLC

[motormad]

Strange , what of i don't know how many there are?

I'l try this later.

[RichardL]

Excel sheet can have greater area than you intend. This was my advice to colleague who sent me a large sheet. "When you next modify that spreadsheet, please go to each sheet, and select from row 200 (below any data), to the end, delete, 'entire row', then save, it will save to 100kB instead of 8MB." I guess that will reduce your line-count.

[Spiff59]

There's a modified version of _ExcelReadSheetToArray() sitting in the BugTrack system.

Am curious if it behaves the same?

I'll stash it in a spolier below...

expandcollapse popup

Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
; Parameter edits
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iStartRow < 1 Then Return SetError(2, 0, 0)
If $iStartColumn < 1 Then Return SetError(2, 1, 0)
If $iRowCnt < 0 Then Return SetError(3, 0, 0)
If $iColCnt < 0 Then Return SetError(3, 1, 0)
Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count
Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count
If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result
     Local $avRET[1][2] = [[0, 0]]
     Return $avRET
EndIf
; Parameter edits (continued)
If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts
If $iRowCnt Then
     $iLastRow = $iStartRow + $iRowCnt - 1
Else
     $iRowCnt = $iLastRow - $iStartRow + 1
EndIf
If $iColCnt Then
     $iLastColumn = $iStartColumn + $iColCnt - 1
Else
     $iColCnt = $iLastColumn - $iStartColumn + 1
EndIf
; Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value
; Handle single-cell sheet
If Not IsArray($aArray) Then
     Local $avRET[2][2] = [[1, 1]]
$avRET[1][Not $iColShift] = $aArray
     Return $avRET
EndIf
; Insert Row-0 totals, convert Col/Row array (from Excel) to Row/Col, apply $iColShift
Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]]
For $i = 1 To $iColCnt
     For $j = 1 To $iRowCnt
         $avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1]
     Next
Next
Return $avRET
EndFunc ;==>_ExcelReadSheetToArray

Edit: I'd try it myself, except your download stops at 394K.

Edited August 5, 2012 by Spiff59

[motormad]

Spiff59

That function works better.

It loads the sheet without crashing.

But stil it loads also a bunch of empty cells. (42000 + )

RichardL

I uploaded an excel again.

cant get mine smaller... strange

if you uplaod this one with autoit it gives 10 rows ??

It only has 9

111.zip

Edited August 7, 2012 by motormad

[Spiff59]

I have spreadsheets I work with that have chronological entries with the oldest entry at the bottom. I looked in a script that, among other things, gets the latest date out of the first column of that spreadsheet. I must have encountered the same issue, as the script has a loop that manually searches backwards through what is returned by _ExcelReadSheetToArray() looking for the last populated column.

[water]

Used rows doesn't only mean "rows with data" but includes all rows that ever have been touched including: "Formatted Cells will count as 'Used' and add to your used row total."

[RichardL]

On that 111.xls you posted in #6, if you ^A it selects A1:V9, but if you press ^Shift-End the last cell is Z10. A quick look in VBA ? activesheet.usedrange.address -> $A$1:$Z$10

[motormad]

RichardL

Ok,

Thats true,... But how does this help me ?

I'd like 2 learn more

Edited August 7, 2012 by motormad

[Reg2Post]

@OP:

Is your script 2 lines of code?

How are you using:

$iRows = $oExcel.ActiveSheet.UsedRange.Rows.Count ?

In otherwords, what are you doing with the $iRows var other than just counting the last row that the used range is?

As others pointed out, the used range can include empty but formatted cells.

You could try the following (which I am testing out right now):

$LNC = $oExcel.Application.Activesheet.Range("A:A").End(-4121).Address

$LNC will be the last non-empty cell in the "A:A" range***. Then put that var as the "Count of rows to read" ($iRowCnt) Parameter in _ExcelReadSheetToArray that you are trying to use.

***Note: This will not work if you have blank cell(s) in between your range. It will only go to the first non-empty cell in the column that is before an empty cell (top-down direction).

[MrMitchell]

Not sure if this will help, but this will return last row:

$row = $oExcel.Application.Selection.SpecialCells(11).Row

On a blank worksheet it just returns "1". The "11" refers to $xlCellTypeLastCell Enumeration http://msdn.microsoft.com/en-us/library/office/aa213567%28v=office.11%29.aspx

I'm just not sure of how "xlCellTypeLastCell" is defined in terms of how it handles blank but formatted cells, etc...

[Reg2Post]

Not sure if this will help, but this will return last row:

$row = $oExcel.Application.Selection.SpecialCells(11).Row

Nice find but in my test it returned the same value as .ActiveSheet.UsedRange.Rows.Count which includes empty cells and formatted empty cells in the used range. Looks to be looking for the row that has the last non-empty cell in a used range.

[RichardL]

I've just had a look at Excel.au3, and I see the line using Application.Selection.SpecialCells(..., but 'Selection' usually refers to the range the user has selected with with keyboard+mouse. For programmed access you would have to set the selection. Maybe this has been put right in the latest Excel.au3. Similarly the use of ActiveSheet is relying on luck (or only 1 sheet). For programmed access better to use something like Application.Worksheets(n).cells(x,y) (Check in VBA before use).

[MrMitchell]

Here's another way...this one worked for me actually:

$lastCell = $oExcel.Intersect($oExcel.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByRows, $xlPrevious).EntireRow, $oExcel.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByColumns, $xlPrevious).EntireColumn)
MsgBox(0, "", "Row:" & $lastCell.Row & ", Col:" & $lastCell.Column)
;This next line for comparison to the previous line
MsgBox(0, "", $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Row)

[motormad]

Thanks all , this works perfect.I use $lastCell.Row (from MrMitchell) in the FunctionExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $lastCell.Row , $iColCnt = 0, $iColShift = 0)

didnt try the spmmution of water yet

Edited August 8, 2012 by motormad