Make print area dynamic & replace a SUMPRODUCT with code!

Hello!

I have this code

Please Login or Register  to view this content.

i'd like to modify in the last part the code(the print part) do the named range "myrange" to be dynamic!

Also i use this SUMPRODUCT formula to get back some data from my other sheet.

Formula:

Please Login or Register  to view this content.

As my data sheet will include more that 10000 rows of data i know that the calculations will be too slow..

So can we replace this SUMPRODUCT formula with a code to work faster?

Note: I use SUMPRODUCT because this workbook works also in computers yhan use Excel 2003

Thanks in advance.

If your actual data set is only 20 rows you could do that sumproduct as a loop without slowing things down too much:

Please Login or Register  to view this content.

I'm not entirely sure what you mean by make the named range dynamic - dynamically based on what?

My Named range, "myrange" use this formula.

=Macro!$A$5:$C$22

So will print only the data in this range.

Saying that i need a dynamic range i mean that i need to print all the data for eatch time.

Sometimes will be range A5:C20, ANOTHER a5:c120, another A5:C75 and so on . This is what i mean dynamically. Based on the last row filled with data.

..If your actual data set is only 20 rows you could do that sumproduct as a loop without slowing things down too much:

No as i said in my first post..

.

.As my data sheet will include more that 10000 rows of data i know that the calculations will be too slow..

For 20 or 100 rows no need of code. Formula works great! But for more than 10000 rows..?

Try:

Please Login or Register  to view this content.

I can't think of way to code that sumproduct formula efficiently. I'd probably approach it by assigning the formula it to a cell and then reading the cell value.

No. This not works because printing Everything that exist in the whole sheet...

I need to print the range A5:to the last used cell in Column C.

This works! Thank you.

Can you pls explain what do you mean by this?

I can't think of way to code that sumproduct formula efficiently. I'd probably approach it by assigning the formula it to a cell and then reading the cell value.

Please Login or Register  to view this content.

Where A1 is a cell on your worksheet not in use you can use to hold a temporary value.

Actually, thinking about it, maybe you could do something like:

Please Login or Register  to view this content.

Perhaps it's sound sill question ..but how can i use your last code?

I guess you would want a custom function?

Please Login or Register  to view this content.

then in your cell
=Sumproduct_Replacement(Data!$A$2:$A$20<,$B$1,Data!$B$2:$B$20,$C$1,Data!$C$2:$C$20)

Thank you!

Is it noticeably quicker? You should probably add screenupdating off/on as well:

Please Login or Register  to view this content.