remove duplicates and delete cells...

I am using the following code to grab installed software on a remote computer through a macro in Excel 2007. I don't have the entire code I'm using as the majority of it works, this section here though is where I'm having problems.

Please Login or Register  to view this content.

This code properly grabs the values I'm looking for and sorts them. So the proper output would be something like this...

......Column A.....Column B
1.....Software.....Microsoft .NET Framework 2.0 Service Pack 1
2..................MSXML 4.0
3..................MSXML 6 Service Pack 2 (KB954459)

That output is actually what I get for the majority of the machines, but there are some where I get multiple values for the same product returned like this...

......Column A.....Column B
1.....Software.....Microsoft .NET Framework 2.0
2..................Microsoft .NET Framework 2.0
3..................MSXML 4.0
4..................MSXML 4.0
5..................MSXML 6 Service Pack 2 (KB954459)

I had some code that removed the duplicates, but I was having a heck of a time deleting the cell in column B that held the duplicate value along with the corresponding blank cell in column A.

I ended up scrapping the code after pulling out my hair! So my question to y'all is do I look for logic that stores the values I'm getting from the remote machine's registry into an array, sort the array and remove duplicates or have Excel do the work for me after the fact?

Either way I have not been successful in my googling to find code showing a way to do either of the above solutions.

Forgive my coding (or lack there of) as I'm not knowledgable in the ways of VB (I overthought projects too much when I tried for a computer science major in college...ended up with a degree in communications instead).

Thanks so much in advance,
John

Hello Jon,

Welcome to the Forum!

The code I have added will remove duplicates in columns "A" and "B". The deletion process starts at the end of range and goes up. Where N is the last row, if row N - 1 = row N then cells "A" and "B" of row N - 1 are deleted. If cell "A" of row N - 1 has a value then that value is copied to cell "A" of row N before row N - 1 is deleted.

Please Login or Register  to view this content.

Commenting out the line..

Please Login or Register  to view this content.

...didn't make a difference. I thought maybe the non-blank cell value in Column A in the range was affecting the outcome, but that's not the case.

I have found out that depending on the number of duplicates, that's how many blank cells are left over in Columns A and B.

Thanks,
John