B
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.
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.