Find "single row" and delete them

Hi everyone,
I am pretty new to Excel - but I have a little programming experience.
Anyway, I am stuck and could need help.

I tried to write a script that does following:


I have data that looks like this:

1
1
2
2
3
3
4
5
5
6
6

As you can see, mostly every two rows contain same values.
But there are some values (here it is 4) that is a "single row".

I want to find every "single row" and delete them.
I tried a long time but reached frustration now
I hope someone can help me.
My script is below.
Thank You!

Please Login or Register  to view this content.

Hello jk@CU,

Here is version similar to yours. The Dictionary object is an Associative Array. This makes it faster and easier to reference the elements because of the Exists method. This will tell you if an element is already in the array. The additional advantage over a regular array is the index or "key" can be any of any data type as can the contents or "item" except a User Define Type.

Please Login or Register  to view this content.

Hi Leith and everyone,

Sadly I recognized, that that macro makes some kind of mistake
1.) It does not delete every "single-value-row"
2.) However it seems to delete too many rows
3.) I could not apply that macro to a selection of a huge number of rows
(e.g. 4000)

I will definitely work on a solution - If someone else is interested to do so before I would be very grateful

I will attach an excel file (without the two macro-versions above).
It contains two columns, the first is the "ID" of a person (here person 13 and 14), the second column contains the values I would like to filter for.

Best, Johann

I just used a minor variation of Leith's code on 10,000 rows, and it worked perfectly. Post a workbook where it doesn't work.

Hello jk@CU,

Please post examples that represent to the ACTUAL data layout and format. The macro works as it should. The problem is you failed to mention that another variable Var1 needed to be considered. I discovered this after manually examining all the cells you wanted to check. I will post back with a macro that takes this new variable into consideration.

Hello jk@CU,

I have finished the new macro. It is setup to allow you to verify its accuracy by coloring all the unique entry cells in column "B" yellow. The macro will display a dialog showing the count of unique cells found and ask if you want to delete the cells' rows.

The macro now creates a Dictionary object for the groups of data based on the value of the cell in column "A". The data in column "B" is added to this group as a new Dictionary object which in turns holds a Range reference to the cell in column "B". Whenever a second reference to the data in a group is found, the data Range reference is set to Nothing. This makes it easy to then create a Range of only unique cells to be deleted. The macro below has been added to the attached workbook. Let me know how this works out.

Please Login or Register  to view this content.

Hello Johann ,

I am glad this version meets your needs. My wife is a psychologist (MFT). I would love to hear more about this as your studies progress. Rapid eye movement desensitization for the treatment of PTSD is a remarkable tool. If your research can help identify personality types by tracking eye movement behaviour, that would be another leap forward in the field. Good luck and stay in contact.