[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

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

G'day there Frank,

Hi

Howdy.

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

I've got it up on my monitor as we speak. However I think that it
may be of more use to me if I actually read it. I've found that my
monitor isn't telepathic and doesn't impart wisdom unless I take note of
what's there =).

Thanks muchly for your assistance,
See ya
Ken McLennan
Qld, Australia