santo

Excel row count

8 posts in this topic

Hi,

Is there any way to find the number of rows in an excel sheet

Share this post


Link to post
Share on other sites



Use the "UsedRange" property: "$oExcel.ActiveSheet.UsedRange.Rows.Count"


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

Use the "UsedRange" property: "$oExcel.ActiveSheet.UsedRange.Rows.Count"

Thank You

Share this post


Link to post
Share on other sites

#4 ·  Posted

Will this work even if the sheet has a filter??? I only want the count after the filter is applied.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

It appears to count all rows.  I used this in VBA and it only shows the "visible" row count after the filter is applied.

ActiveSheet.AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Count

so I would think this would be the UDF equivalent (untested)

$oExcel.ActiveSheet.AutoFilter.Range.Rows.SpecialCells($xlCellTypeVisible).Count

 

edit:

There appears to be an Excel UDF function which may collect this info

_Excel_FilterGet

 

Edited by spudw2k

Share this post


Link to post
Share on other sites

#6 ·  Posted

How to retrieve filtered rows is describd in the wiki.

1 person likes this

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

#7 ·  Posted

You could try  _FileCountLines to see if it differentiates between filtered and non-filtered spreadsheets.


Meds.  They're not just for breakfast anymore. :'(

Share this post


Link to post
Share on other sites

#8 ·  Posted

_FileCountLines only works for flat files, not for Excel spreadsheets!


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

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