Excel row count

[santo]

Hi,

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

[water]

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

[santo]

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

Thank You

[XinYoung]

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

[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 April 10, 2017 by spudw2k

[water]

How to retrieve filtered rows is describd in the wiki.

[aleph01]

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

[water]

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