COUNTIF Macro

[tatendatiffany]

My PC is running out of resources because i have used multiple countif, sum statements for dynamic ranges and i am trying to use macros instead :

e.g.

=SUM(OFFSET(KTPT81T!G5,0,0,COUNT(KTPT81T!G:G)))

the data orginates from sheet 126 and i need the sum to be displayed in sheet 1 column Y2. Please help me

and =COUNTIF(TABF10!$F:F,"Y") for a countif statement

Many Thanks

 

[GlennUK]

Do not reference entire columns. Unless you know that you are going to be getting around 65000 rows of data ( or worse, if you are in Excel 2007+ format, 1048576 rows of data ) .... do you have an estimate of the maximum possible extent of your data?

 

[tatendatiffany]

Thanks again fro replying, the thing is the range is going yo be dynamic and data will be continually added to the table, so ideally it should be from range G:G or H:H depending on the column. I am using excel 2007

Thanks again

 

[GlennUK]

Are you saying that you have no idea of the maximum number of entries?

How many entries are there now? How much has your range increased during the last month?

 

[tatendatiffany]

Right now the column has data up to G50, the data entries will vary no more than 10 every month but i want it to be dynamic so it automatically updates when there changes are made.

Thank you

 

[GlennUK]

So a range of 1000 rows would be sufficient then?

Change ALL of your entire column references to reference ranges up to row 1000 instead. ( or even 2000, or 5000, if you think 1000 isn't enough ). This will be a massive improvement and should remedy your resources problems.

 

[tatendatiffany]

I have changed it but its still saying running out of resuorces, what else couldo i do (i think i forgot to mention) i have 28 worksheets i have to do the same to and they all have different ranges H:KL. i dont know what else to change someone had told me using macros is faster and fixes the problem

Thank you