I was confused by this line:

Column AC is the one with names only, no blanks, sorted
alphabetically when added via an AddPatient() routine.

There was a disconnect between the no blanks in one of your posts and the
discontiguous range in others.

So is there anything else in Column AC except for each name and empty cells?

If no, you could still pickup that range and loop through them:

dim myListRange as range
dim myCell as range
with whateversheet
set mylistrange = .range("ac1",.cells(.rows.count,"AC").end(xlup)
end with

for each mycell in mylistrange.cells
if isempty(mycell.value) then
'do nothing
else
'do the find
end if
next mycell



David wrote:

Responses in line:

Dave Peterson wrote

I'm confused about column AC.

You defined a range name based on discontiguous cells in column A.
But in this message, you say that AC is a single list with no blanks
(and no duplicates)?

No. Range name myList is based on discontiguous cells in column AC.
PrintMine() finds those names in column A.

If it's really a simple list that can grow or contract--no blanks, no
duplicates--you could use a dynamic name that grows and contracts with
the amount of data in that column.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Then you could still use that name in your code and loop through each
cell--or pick it up as an array and loop through the elements of the
array.

Take a look at the dynamic range name on Debra's site. You may still
be celebrating.


I've seen Debra's site. Dynamic range name formulas appear to refer to
all cells in a column (with maybe an allowance for starting row in the
2nd argument) - no accommodation for discontiguous cells within that
column.

Am I missing something?

--
David

David wrote:

Dave Peterson wrote

I was thinking that if the names only appeared in column A (and
nothing else),

Not pheasible. Column A contains all the names all right, but each is
followed by a group of classes and a Total -- no empty cells. And
it's there where horizontal page breaks (HPB's) in PrintMine() come
into play.

PrintMine() scans column A until it finds a name from my caseload and
then prints that page.

Column AC is the one with names only, no blanks, sorted
alphabetically when added via an AddPatient() routine.

Column AC was not originally intended for use in PrintMine(), but a
list for Data Validation. But I thought I might use it also as a
source for a range name holding my caseload.

But as you pointed out (and I'm glad you did before I celebrated too
much), this workbook is often updated as patients join and leave the
program.

My caseload doesn't change THAT often, so changing hard-coded names
in PrintMine() isn't THAT big a deal. For now I've reverted to that
and abandoned the range name approach.

--
David


--

Dave Peterson