Sign in to follow this  
Followers 0
motormad

Excel count rows

16 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by xeroTechnologiesLLC

Share this post


Link to post
Share on other sites

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

I'l try this later.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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...

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 by Spiff59

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 Posted Image

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

It only has 9

111.zip

Edited by motormad

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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."


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

RichardL

Ok,

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

I'd like 2 learn more :graduated:

Edited by motormad

Share this post


Link to post
Share on other sites

@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).

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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

:ILA3:

Edited by motormad

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  
Followers 0