Range question

[Jock]

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

 

[Don Guillett]

Use a defined name such as myrng defined as
insert>name>define>name it myrng>in the refers to box type
=offset($b$7,0,0,counta($b:$b)-6,1)

 

[Jock]

Thanks Don, I have set up the named range but I am unsure how to apply your
formula into mine.
--
Traa Dy Liooar

Jock

Use a defined name such as myrng defined as
insert>name>define>name it myrng>in the refers to box type
=offset($b$7,0,0,counta($b:$b)-6,1)

 

[Don Guillett]

=COUNTIF(Sheet1!B$7:B$7000,D2)
=COUNTIF(myrng,D2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks Don, I have set up the named range but I am unsure how to apply
your
formula into mine.