I agree, SELECT COUNT(*), RecordCount, GetRows all work as expected when the
UsedRange matches the real data.

But all fail with a flawed UsedRange. Excel4Macros failed to run on the
largest of the 2 wbks but not on the smallest.

Unfortunately I have no quality control over these received wbks. Sometimes
the quality is appalling, hidden rows, hidden columns, end of line
characters, merged cells, cell errors, autofilters, some even without field
names and of course some with flawed UsedRanges.

I would be willing to supply the 2 wbks with known flaws were it not for
data protection. They contain names, job titles, telephone numbers etc and
it would be wrong of me to share those details. And of course if I deleted
or overwrote the data the ensuing save would reset the UsedRange.

But if you know of a way to create a UsedRange which is out of line then I
am certain you would make the same observations.

Geoff

"RB Smissaert" wrote:

It works fine with me.
Could you mail me that workbook that gives you the wrong answer?

RBS


"Geoff K" wrote in message
...
Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as
50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid
the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only
about
2 wbks in 500. But because of the risk, I have to use the slow method on
every wbook. It would be great if I could detect a flawed UsedRange and
run
the 2 recordset method on that wbk only. On the rest of the wbks I could
use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because it
is
so blisteringly quick. I think instead it probably uses the UsedRange
last
row or something like it. Unfortunately a null is a record to SQL so if
the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead: