[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