Copy Unique Values from ranges in 3 sheets and Paste Values (not Formulas) to one column in another sheet in the same workbook

[txjeffm]

I am a fairly new VBA user but have worked heavily in Excel for years. I am trying to take advantage of VBA to simplify my life, but am running into some difficulty. I use Excel 2007.

I have a workbook that uses formulas to take whatever data is input into certain range and concatenate the base part #, revision level and color code followed by "***" followed by the description, followed by "****", followed by the supplier name.

In essense, I have 3 ranges (sheet "1st Row" range LT28:MK5000, sheet "2nd Row" range D225:EN5000 and sheet "3rd Row" range BG24:BU5000) in which each cell of each range could have something like "JK0372607AB02****Product ABC****XYZ Corp" in it.

Each cell could be a duplicate of another cell on any of the other tabs, or it could be unique, each string could be of varying lengths and have numbers and/or letters and the lengths of the collumns could vary from range to range (I went to 5000 only becuase I know I will never have data beyond that row) but all collumns in each seperate range would be the same length. Also, all there are no blank columns within each range.

What I ultimatley want to do is to have macro that goes to sheet "Combined", clears whatever data is in Collumn A starting with row 2, then looks at the designated ranges on each of the other 3 sheets ("1st Row", "2nd Row" and "3rd Row"), extracts only the unique values, and pastes them all into a single column in worksheet "Combined" starting at Cell A2. Then, anytime I need to update it, it is just update the data in the other tabs and re-run the macro.

It seems like it would be pretty simple, but so far I am stumped.

I have tried searching the boards and combining bits and pieces of what I find here and there, but so far, all I have been able to do is confuse myself and make it do one range, but it copies ALL cells in the first sheet (not unique values) and it pastes the Formulas, not the values.

Here is a sample of what the data could look like (only there would be several more collumns and many more unique values):

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD][/TD]
[TD]DF037212AB02****Product 12356****Dyna Corp
[/TD]
[TD]JK0372607AB02****Product ABC****XYZ Corp
[/TD]
[/TR]
[TR]
[TD]22760821****Product ABC****XYZ Corp
[/TD]
[TD]JK0372607AB02****Product ABC****XYZ Corp
[/TD]
[TD]JK0372607AB02****Product ABC****XYZ Corp
[/TD]
[/TR]
[TR]
[TD]DF037212AB02****Product 12356****Dyna Corp
[/TD]
[TD][/TD]
[TD]12345698712****Product ABC****XYZ Corp
[/TD]
[/TR]
[TR]
[TD]JK0372607AB02****Product ABC****XYZ Corp
[/TD]
[TD]JK0372607AB02****Product ABC****XYZ Corp
[/TD]
[TD]DF037212AB02****Product 12356****Dyna Corp
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]














Desired Output would be:

[TABLE="width: 387"]
<TBODY>[TR]
[TD]12345698712****Product ABC****XYZ Corp</SPAN>
[/TD]
[/TR]
[TR]
[TD]22760821****Product ABC****XYZ Corp</SPAN>
[/TD]
[/TR]
[TR]
[TD]DF037212AB02****Product 12356****Dyna Corp</SPAN>
[/TD]
[/TR]
[TR]
[TD]JK0372607AB02****Product ABC****XYZ Corp</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Any help would be huge. I update this file multiple times throughout the day, and currently, it involves copying each collumn of each range, pasting special values into the "Combined" tab below the last cell, then removing duplicates and zeros. Not only it is time consuming and tedious, it is frustrating as heck because I know there is an easier way...I just have not been able to figure it out yet. So, after 4 weeks of spending hours each day going through various sites and trying my own hand at developing something...I am BEGGING for help!!
[TABLE="width: 81"]
<TBODY>[TR]
[TD]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]