Select part of column that contains data

G

grrarrgh

1.I have 2 columns of contiguous data that i want to put into linest(),
they are sorted by date in the rows.
2.They may be different lengths, but usually end at the same row.
3.Is there a quick VBA equivalent to doing ctrl-shift-down

Separate Q: Is there a quick way to pick out the first and last rows
that have data in both columns?

thanks
 
F

Frank Kabel

Hi
for some of your questions:
3. Question:
use activecell.formulaarray="=array_formula"

Separate question. One way: Use the following array formulas
=INDEX(A:A,MIN(IF(A:A<>"",ROW(A:A))))

and
=INDEX(A:A,MAX(IF(A:A<>"",ROW(A:A))))
 
A

Aladin Akyurek

Separate Q: Is there a quick way to pick out the first and last rows
that have data in both columns?
Click to expand...

Lets say that we have the following in A:B from A1 on...

{"","";2,"";1,3;6,7;5,4;6,9;2,7;6,9;"",6;"",9}

meaning A1 empty, B1 empty, A2 = 1, B2 = 3, etc. Since the data of interest
is numeric...

D2:

=MATCH(9.99999999999999E+307,A:A)

D3:

=MATCH(9.99999999999999E+307,B:B)

D4:

=MIN(D2:D3)

which gives you the last row both references (that is, column A and column
B) have a numeric value.

E2:

=MIN(IF(A1:INDEX(A:A,D2),ROW(A1:INDEX(A:A,D2))))

which must be confirmed with control+shift+enter instead of just enter.

E3:

=MIN(IF(B1:INDEX(B:B,D3),ROW(A1:INDEX(B:B,D3))))

which also must be confirmed with control+shift+enter instead of just enter.

E4:

=MAX(E2:E3)

which gives you, all things being equal, the first row both references have
a numeric value.

Now you can construct the common range for the references using INDEX,
OFFSET... as in:

=LINEST(INDEX(A:A,E4):INDEX(A:A,D4),INDEX(B:B,E4):INDEX(B:B,D4),...)
 
Top