SumProduct not Working in a Macro

[Booey]

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.

 

[Jacob Skaria]

Why do you need to use SUMPRODUCT(). Try
Range("I1:I10").Formula = "=COUNTIF($H$1:$H$10,H1)"

OR try the below macro instead which use the Advanced Filter option

Sub Macro2()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:G" & lngLastRow).AdvancedFilter Action:=xlFilterInPlace,
Unique:=True
For lngRow = 1 To lngLastRow
If Rows(lngRow).Hidden Then Rows(lngRow).Delete
Next
ActiveSheet.ShowAllData
End Sub

 

[Joe User]

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]

Hi Jacob. I need to use SUMPRODUCT as COUNTIF only works on cell values up
to 255 characters, whereas this is not a problem with SUMPRODUCT. Some of
the concatenated cells might be as much as 1000 characters.

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.

Thanks for trying to help.

 

[Booey]

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.

 

[Jacob Skaria]

Did you try the macro..
--
Jacob

Hi Jacob. I need to use SUMPRODUCT as COUNTIF only works on cell values up
to 255 characters, whereas this is not a problem with SUMPRODUCT. Some of
the concatenated cells might be as much as 1000 characters.

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.

Thanks for trying to help.

 

[Bob Phillips]

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)")

If you are going to use COUNTIF there is no need for evaluate, that is only
necessary for array formulae.