Jump to content

Excel count rows


Recommended Posts

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

Link to comment
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.

Link to comment
Share on other sites

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
Link to comment
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.

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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).

Link to comment
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...

Link to comment
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.
Link to comment
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).

Link to comment
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)
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...