You still have not explained what you want done with all the extra cells.
I will take a wild guess and assume that you have a constant model (D3
6)
and a data loading process that puts exactly four numeric values in proper
order, but with an unpredictable number of line feeds (<=16) in and around
the data. As a result, I am guessing that you want to align the first
numeric value in C3:C18 with D3, the second numeric value in C3:C18 with D4,
....
The most reliable way to do this in VBA is to loop through the cells in
C3:C18 and populate an array (dimensioned to hold four values) with the
values that you find. That way you can include a diagnostic as to whether
there really are exactly four values or not.
Doing it in an array formula is a bit tricky. If the values in C3:C18 will
always be ascending, you could use
=LINEST(SMALL(C3:C18,{1;2;3;4}),D3
6)
Otherwise the problem becomes much more messy. The following will work for
the limited size problem that you posed, but does not extend to large sample
sizes
=LINEST(CHOOSE(SMALL(IF(ISNUMBER(C3:C18),ROW(C3:C18)),{1;2;3;4})-2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18),D3
6)
The more obvious approaches using INDEX(), OFFSET(), or INDIRECT() fail,
because their results are not recognized as arrays within array formulas.
Note that the VBA function EVALUATE() will evaluate the string argument as
though it were an array entered Excel expression.
Jerry
