Jump to content
santo

Excel row count

Recommended Posts

santo

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
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
santo

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

Thank You

Share this post


Link to post
Share on other sites
XinYoung

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
spudw2k

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
water

How to retrieve filtered rows is describd in the wiki.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
aleph01

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
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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

×