Range question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jock - 19 Mar 2008 15:58 GMT

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

Signature

Traa Dy Liooar

Jock

Reply to this Message

Don Guillett - 19 Mar 2008 16:10 GMT

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)

>enter>now use that in your formula

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> 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.
[quoted text clipped - 9 lines]
>
> tia

Reply to this Message

Jock - 19 Mar 2008 17:30 GMT

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

Signature

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
[quoted text clipped - 13 lines]
> >
> > tia

Reply to this Message

Don Guillett - 19 Mar 2008 17:36 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Thanks Don, I have set up the named range but I am unsure how to apply
> your
[quoted text clipped - 19 lines]
>> >
>> > tia

Reply to this Message