Counting the number of rows with data in an excel fille

[VeryGut]

So, I have stumbled upon a problem with countring rows in excel files.

The script I am working on will count the total number of rows of every file in the folder it was placed into.

It is to my understanding that UsedRange counts rows even if they are empty, provided that a user has previously edited them. In other words, even if the file has two rows, the script might return 1000, if I edit and then leave empty cell (A,1000). How can I avoid this happening?

Thanks in advance!

 

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>



Global $Files = _FileListToArray (@ScriptDir, "*")
$oExcel = _Excel_Open()
$TotalNumberOfRows = 0

for $i=2 to $Files[0]
      Sleep(1000)
      $sWorkbook = @ScriptDir & "\" & $Files[$i]
      Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
      Sleep(2000)
      $NumberOfRows = $oExcel.ActiveSheet.UsedRange.Rows.Count
      $TotalNumberOfRows= $TotalNumberOfRows + $NumberOfRows
      MsgBox(1, "", "Number of invoices: " & $TotalNumberOfRows)
      Sleep(100)
      _Excel_BookClose ($oWorkbook, False)
Next

 

[Subz]

Do you have an example spreadsheet that displays the behavior?  Haven't been able to replicated it myself.  Also wouldn't CountA give you a more accurate count as it will skip any blanks?

Example:

$oRange = $oExcel.ActiveSheet.Range("A:A")
MsgBox(4096, "Excel Count Non-Blank Cells", $oExcel.WorksheetFunction.CountA($oRange))

 

Edited May 31, 2017 by Subz

[water]

The wiki describes how to work with ranges. Section "Last Cell" has a link to a very good site describing how to get the last cell ignoring empty cells.

[water]

The following script ignores all "touched" cells in column A:
 

#include <Excel.au3>
; XlDirection enumeration: https://msdn.microsoft.com/en-us/library/office/ff820880.aspxGlobal $oExcel = _Excel_Open()
Global $xlup = -4162
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")
Global $iRow = $oWorkbook.ActiveSheet.Range("A65536").End($xlup).Row
MsgBox(0, "", $iRow)

 

[VeryGut]

  On 5/31/2017 at 2:36 PM, water said:

The following script ignores all "touched" cells in column A:
 

#include <Excel.au3>
; XlDirection enumeration: https://msdn.microsoft.com/en-us/library/office/ff820880.aspxGlobal $oExcel = _Excel_Open()
Global $xlup = -4162
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")
Global $iRow = $oWorkbook.ActiveSheet.Range("A65536").End($xlup).Row
MsgBox(0, "", $iRow)

 

Expand  

This is exactly what i was looking for!

 

Thanks!

[water]

Glad to be of service