Changing VBA code to work on different column

[Hecky83150]

Hello, so I have this VBA code which works well, but now I need it to work on different columns.

Please Login or Register  to view this content.

Basically right now, it checks all cells in column A and if there are some with same name, it count their values in column C and then remove rows with duplicate names.

Now what I need to change is that cell names will be in Column B and it count values in same column.

So from A and C to B and C.
Could you help me with this please?

[mehmetcik]

How many rows of data do you have?
Your code would is amazingly slow for any significant amount of rows.


The "A" in these lines tells the code to look in column A. So Change "A" to "B"

Please Login or Register  to view this content.

The 2 in this line tells the code to increment the column two columns to the right of Column A

Please Login or Register  to view this content.

So if to are now looking at column B and want to increment column C the 2 needs to be a 1

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Hecky83150]

Hi, thanks for the reply.

I have changed it to this but it seems to not work properly. It only remove 1 duplicate and ignore multiple ones.

Please Login or Register  to view this content.

[Kaper]

You may try such (only slightly ammended) code:

Please Login or Register  to view this content.

I think the first code worked well only because in your file content of columns a and B was either the same or there were the same pairs. because you compared column A and B

Please Login or Register  to view this content.

As I don't like manipulating control variable in For ... Next loop, I also changed inner row counter to go bottom-up. This way deleted row changes nothing in current loop execution, because in next turn the code will check next row up anyway.

Also, as mehmetcik mentioned such code will probably be rather slow in case of large datasets, with many duplicates. Probably the quickest (built in) way to deal with such situation will be Pivot Table.

[mehmetcik]

Apologies

There is a much faster method using evaluate that I was trying to make work.

In the interim. This code should be a lot faster than your original.

Please Login or Register  to view this content.

[Hecky83150]

Apologies

There is a much faster method using evaluate that I was trying to make work.

In the interim. This code should be a lot faster than your original.

Please Login or Register  to view this content.

Looks good to me I don't mind using different code if it works.
I've tried to execute this code, but it ran error

Attachment 666515

and highlighted this line

Please Login or Register  to view this content.

[Hecky83150]

Thank you, this one works for my purpose. I think the biggest datasets are going to be maybe in thousands rows tops, do you think it will couse problems?

When I've tried to run this with couple hundreds of datasets if worked fast I think, it was in about 1 second done.

[mehmetcik]

It is best to use this code. The evaluate function can only cope with 64000 rows.