Adapt an array routine or a better way?

[Stuart]

I build an array of the instances of '£' when found in
col(6). An offset down of a couple of rows will mark
the start of a data 'page', whilst an offset up from the
next value held in the array, will mark the end of that
data 'page'.

Once the array is built, this code establishes the data
'page' to be transferred:

Set rng1 = Range(varr(i).Offset(1, -5), varr(i + 1) _
.Offset(-6, -5)).Resize(, 6)

This seems to work until I reach the last 'page' when
suddenly there is no '£' in col(6) to mark the next
'page'......because there isn't a next page, and so
no '£' to Offset back up from!

What there is however, consistently throughout the data,
is a '£' in col(5) which more accurately marks the end
of the data 'page'.

So might there be a more efficient way that does
(perhaps) something like this:

search down col(6) for a '£' & mark it as a 'page' start
then switch to col(5) in that same row
search down col(5) for a '£' & mark it as a 'page' end
etc

Would be grateful to know if arrays are the answer, and
for any help, please.

Regards.

 

[Harlan Grove]

...
...

This seems to work until I reach the last 'page' when
suddenly there is no '£' in col(6) to mark the next
'page'......because there isn't a next page, and so
no '£' to Offset back up from!

...

If there's nothing in the cells below the last bit of data, you may want to move
to the bottom of the worksheet, then use .End to move up to the last row
containing anything, then offset down (and right?) from that cell to enter a '£'
in col 6, then run your existing procedure, and once it's completed delete the
added '£' in col 6.

 

[Stuart]

Understood, many thanks.

Regards.

...
..
..

If there's nothing in the cells below the last bit of data, you may want to move
to the bottom of the worksheet, then use .End to move up to the last row
containing anything, then offset down (and right?) from that cell to enter a '£'
in col 6, then run your existing procedure, and once it's completed delete the
added '£' in col 6.

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something

else.