Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Range question
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Range question
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Range question
Thanks Don, I have set up the named range but I am unsure how to apply your
formula into mine. -- Traa Dy Liooar Jock "Don Guillett" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Question | Excel Discussion (Misc queries) | |||
Range Question | Excel Discussion (Misc queries) | |||
sumproduct range question | Excel Worksheet Functions | |||
Range question in '97 | Excel Discussion (Misc queries) | |||
Question on LABEL Range | Excel Discussion (Misc queries) |