Use a defined name such as myrng defined as
insertnamedefinename it myrngin the refers to box type
=offset($b$7,0,0,counta($b:$b)-6,1)
enternow use that in your formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jock" wrote in message
...
On a large worksheet with approx 7000 rows which will be filled in as time
goes on, I have numerous formulae which refer to ranges; i.e. B7:B7000.
I would like to speed up calculations by having excel only calculate down
to
the last cell which has data in it rather than all the way down to the
end.
Is there a way to do this in a formula and also in vba code?
I.E.
=COUNTIF(Sheet1!B$7:B$7000,D2)

....If Not Intersect(Target, Me.Range("O7:O7000")) Is Nothing Then.....

tia

--
Traa Dy Liooar

Jock