need "best" way to file # of rows in excel (in VBA) 1

[Lynx03]

I know there are several ways to do this but I was wondering if there is a "best" way (and why) to find the number of rows in an excel spreadsheet through VBA code.

Thank you!

 

[xlbo]

Three is no "best" way - just the most appropriate to your worksheet
Personally, I like
cells(65536,1).end(xlup).row

but the limitation there is that it is checking 1 column only

The find method is good but you need to make sure you specify ALL the find options as otherwise it can pick them up from a previous "find" and give an incorrect result

You should definitely NOT loop and end(xldown) is prone to errors if there are gaps in the dataset

Usedrange.rows.count is a nice easy way but usedrange can be incorrect so it's best not to use it too much (especially where data sets expand and contract regularly)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244