Hi
have a look at the following discussion for other way determining the
last row of data (for numers / text)
http://tinyurl.com/33uwo

--
Regards
Frank Kabel
Frankfurt, Germany

Ken McLennan wrote:
[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.]

G'day there People,

First, I should say how useful this ng has been. All my meagre
abilities with VB have come from here. Therefore "Thankyou" to all
who
have contributed.

I've found myself with a problem I can't find a way around. I have
a workbook consisting of 2 sheets. The one I use to store data
contains a bunch of lists (6 I think), each is only a handful of
columns wide and each has a title row. I've named them all using
similar to this:

=OFFSET(Data!$S$1,1,0,COUNTA(Data!$S:$S)-1,5)

Naturally the colums vary according to what the list stores. (The
formula isn't mine, I found it on a tutorial site and adapted it <g
).

I've now found that I'm nowhere near as smart as I thought I was.
The formula works fine, until I come to delete a record from the
list.
That gives me a blank row, which the formula detects and stops the
range there. There could be anything up to 20 or 30 records below it,
but it doesn't detect them.

I need to re-sort the row to consolidate the records into a single
block. Adding records is no problem as the formula detects the
complete recordset and I can sort away like mad.

I've thought of applying an autofilter, collecting non-blanks to a
temp storage area, sorting and then putting the whole thing back to
its original spot (after blanking the original range), but that seems
rather clumsy although it may be the fastest way.

Also, I don't have a problem with blank cells forming partial
records as the first cell of each record is always filled.

Does anybody know a better method?

Have I explained this properly?

Thanks for listening
Ken McLennan
Qld Australia