There's a couple things I see that might be worth some time &
attention...
A dynamic range doesn't allow for blank rows between its beginning/end
cells in the column that --defines-- the range as 'dynamic'. In your
sample data I assume colA defines the dynamic range and so this should
be no problem.
===
You say you can't change the formulas but I strongly suggest you do so
your project has what's needed to result the expected behavior built
into its design. For instance, you can modify both formulas to return
values that compliment your desired sorting preferences. For example,
change the formula in colB to return "Z" instead of a space character,
so those cells end up at the bottom. You can use ConditionalFormatting
to shade the font so the cell 'appears' empty.
This won't alter the computational part of your formula. It merely
alters the return value if the formula fails to compute. (Assumes
formula is wrapped in an IF() function)
===
<FWIW
I'm not sure why people insist on formulas to return a space character
when an empty string ("") is a better choice. Note that the space
character doesn't work in calculations; an empty string does work!
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion