Another way is to create a separate dynamic range for each of
the 3 ranges needed. I thought just using a single dynamic range
would be less confusing (but it does make the formula slightly
longer).
My long VBA project for this use of mine is wrapping up now, after
about 10 days of work and lots of newfound experience at VBA.
I want to thank you and also other people here who responded
in related threads, notably J. Latham, who was also more than
helpful.
I'm sure I'll be tweaking things further in the days, weeks, and
months ahead, but for now, I have a wonderfully table I'll be
using every workday. I may write up parts of the experience
later, and if I do, I'll be sure to post it.
I have a couple of unsolved oddities, and the above is one,
but I'm beginning to suspect a bug or bugs in the program, or
perhaps in an Add-In I have installed. I've now worked around
all these hindrances, but I'll list them for the record. This
is with Excel 2002:
(1) Named ranges that I set with a mixture of relative and absolute
refs go invalid between runs or have the absolute parts shifted as
if they were relative when I try to use the expressions in other
columns. I use the popular Add-In Names Manager, and have the
current version. By way of a workaround, I tried using absolute
refs with INDIRECT and/or OFFSET in these named expressions,
and they fail with an "invalid" error. If I put the identical
expressions in the cells manually, they work as expected.
(2) Some code I enabled that populates columns with conditional-
formatting formulas using relative references for row lines goes
wrong. E.g., I start in Row 2 and have a reference to $E2
and fill that to the bottom row. When the VBA is done, the
table looks wrong; I look in the conditional formats and find
$E3 where I expected $E2. I've double- and triple-checked my
code and my ranges and I don't see any problem. I think there's
a bug somewhere.
(3) The named ranges I tried on my own and the ones you suggested
both would not work. In contrast to the above, when I put these in
the cells manually, they still did work. I now suspect this
is an insidious problem to do with shifting relative references,
analogous to 2. above in another guise.
(4) If I open my workbook and run the macro, it runs fine
and gives the results I want (after I added kludge workarounds
to the above problems, which I'll state below). But if I run the
macro subsequent times, empty, formatted columns (a dozen at a
time or so) are added to the end of the used range. I don't like
them there, because they hinder my navigation, e.g., going to the
last cell. There is no data in the cells. If I close the
sheet and re-open and run the VBA again, they go away. (I
do use a .range.clear function at the top of the macro.)
If I run the VBA multiple times without closing the book,
more and more empty columns are added. I can't figure out what
is causing this, and suspect an Excel bug of some kind. I do
have ADO engines running, etc., and have read that this can
interact with Excel in odd ways, for example.
To solve these problem by kludge method, here's what I did:
(1) I used my mixed-ref named expression in one column, writing
the results to the column successfully and using an "invisible"
font; then, in the other columns in which I want to operate on
the same calculated data, I simply reference the first column's
relative rows instead of using the named expressions again
where there the relative references would otherwise shift.
(2) In the conditional formulas, I was able to apply OFFSET()
instead of relative row refs and make it work as desired.
(3) Shifting the whole long array formula to a named expression
did work, though applying the named ranges within that did not.
Since the formula is out of my way as a named expression and
I can reference it that way, I'm satisfied for now. Again,
I suspect oddly shifting relative refs as the reason for my
trouble with the named ranges.
(3a) Could it be that I've exceeded a length limit on named
expressions, and am causing my problems that way by, I might
speculate, destroying pointer references in the compiler, etc.?
I will have to look more at this idea tomorrow.
(4) My solution to the unwanted columns is this bit of code:
(Have others found a similar problem sometimes? I tried Google ...)
' ouch! This is a kludge fix for a dumb bug
Dim theirRow, theirCol As Long
myRow = lastRow(DestSh) ' sh.Cells.Find(What:="*"...
myCol = lastCol(DestSh) ' sh.Cells.Find(What:="*"...
theirRow = .Cells.SpecialCells(xlLastCell).Row
theirCol = .Cells.SpecialCells(xlLastCell).Column
Debug.Print myRow, myCol, theirRow, theirCol
If theirCol > myCol Then _
.Range(Cells(1, myCol + 1), _
Cells(theirRow, theirCol)).Delete
$16))+1