I have formula below:

=SUMPRODUCT(--(ControlDetail!A2:A63518=A9)*AND(COUNTIF(ControlDe tail!B:B,"*")))

The A63518 range reduces every time I run the code It started out at A65000. The code deletes Duplicate rows in the ControlDetails sheet the the formula reads.
Can anyone help?

I tryed adding $
=SUMPRODUCT(--(ControlDetail!A$2:A$63518=A9)*AND(COUNTIF(Control Detail!B:B,"*")))

can I recommend you consider reading the following webpage on microsoft MSDN site. It concerns the use of Arrays and entire columns. You may want to change your approach completely.

http://support.microsoft.com/default...b;en-us;166342

Cheers

Matt