The problem is with the "TRUE" selection.
It means to search for the "closest" match instead of "exact" match.
I've never had a use for "closest"... so I always use "FALSE".

I tested it, and it worked just fine in your case.

As for use in VBA:

use: Application.WorksheetFunction.Vlookup(......)
as in:

NumberOfRows = Application.WorksheetFunction.CountA(Sheets
(ShtName).Range("A1:A65000"))