Hi Joe.

I eventually solved it using the following code:

mFormula = "SUMPRODUCT(--(" & ConcCol & 2 & ":" & ConcCol & R &
"=" & ConcCol & R & "))"
Q = Application.Evaluate(mFormula)

Where mFormula is defined as String and Q as Long. ConcCol and R are
variables in a For Next loop that I use as I work up from the bottom to the
top of the data.

I had to muck around a bit to get the syntax exactly right in mFormula, but
it works perfectly.

Thanks for trying to help.

--
Regards,
Booey


"Joe User" wrote:

"Booey" wrote:
Range("I10") =
Application.WorksheetFunction.SumProduct(--(H2:H10 = H10))

There are ways to make the WorksheetFunction.SumProduct work.

But try:

Range("I10") = Evaluate("SumProduct(--(H2:H10 = H10))")

or for that functionality, more simply:

Range("I10") = Evaluate("countif(H2:H10,H10)")


----- original message -----

"Booey" wrote:
Can someone please help - this is driving me nuts. I am wanting to use
SumProduct in a Macro but it does not compile.

The reason is that I am writing a macro to delete duplicate rows based on
all columns of data, in some cases up to 50 rows.

A simple example to highlight what I am trying to do - Lets say columns A to
G have data in them in and the data is in rows 2 - 10, headings are in row 1.
Firstly I concatenate all the data across Columns A to G and place the
result in Column H. I do this for all 10 rows (including the heading).

The next thing I do is test column H in row 10 to see how many occurrences
there are in the range H2:H10. If there is more than one I delete the entire
row. I then work my way up to row 2.

If I put the formula "=SUMPRODUCT(--(H2:H10=H10))" into cell I10 in the
spreadsheet itself I get a result no problem, let's say 2.

Whenever I try to use SumProduct in a Macro it won't give a result. To test
it I typed the following in to get a result:

Range("I10") = Application.WorksheetFunction.SumProduct(--(H2:H10 = H10))

When I put this into a Macro the line goes red and the : between the H2:H10
is highlighted and it displays:
Compile Error:
Expected: )

I have tried creating a string variable that contains the "H2:H10 = H10" but
when I put it in it doesn't work. I have tried many other ways but nothing
seems to give me a result that I can work with.
--
Regards,
Booey