Union Ranges on two different sheets

Sure

First range is 2 columns

bob 5
cat 3
dog 4

second range is 2 columns

bob 5
cat 7
dog 19

The final range should be contain these values

cat 3
cat 7
dog 4
dog 19

and the row with bob and 5 should be removed on a double match essentially.

I had to post in this forum so I can upload my file.

http://www.excelforum.com/showthread.php?p=1890075&posted=1#post1890075

Ok. Take one column of data, move it to the bottom of the other, then do another AdvancedFilter on it. If you have headers in row 1 (I'm assuming), then maybe use...



<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> wks3 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> Rng1 <SPAN style="color:#00007F">As</SPAN> Range, Rng2 <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> wks3 = Worksheets(3)<br>    <SPAN style="color:#00007F">Set</SPAN> Rng1 = Worksheets(1).Range("Range1")<br>    <SPAN style="color:#00007F">Set</SPAN> Rng2 = Worksheets(2).Range("Range2")<br>    wks3.Range("A1").Resize(Rng1.Rows.Count, Rng1.Columns.Count).Value = Rng1.Value<br>    wks3.Range("C1").Resize(Rng2.Rows.Count, Rng2.Columns.Count).Value = Rng2.Value<br>    wks3.Range("C2", wks3.Cells(wks3.Rows.Count, 4).End(xlUp)).Cut wks3.Cells(wks3.Rows.Count, 1).End(xlUp).Offset(1)<br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>    wks3.Range("C:D").Delete<br>    <SPAN style="color:#00007F">With</SPAN> wks3.Range("A1", wks3.Cells(wks3.Rows.Count, 2).End(xlUp))<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    wks3.Range("A2", wks3.Cells(wks3.Rows.Count, 2).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, copytorange:=wks3.Range("C1"), unique:=<SPAN style="color:#00007F">True</SPAN></FONT>



Tested roughly on some data, seemed to work for me. You'll end up with no headers (not sure if that is what you wanted or not).

Re: I had to post in this forum so I can upload my file.

hydrojoe11 said:

http://www.excelforum.com/showthread.php?p=1890075&posted=1#post1890075

Click to expand...

http://excelguru.ca/node/7

Ok, sorry but that's still not working yet either, its not removing the bob 4 which is a dupe.

Here is my project


http://www.savefile.com/files/1419379

Here's an alternative:

1. Make sure your data tables have headings (eg Name and Value)
2. Name the data tables "data1" and "data2" (without the quotes).
3. Save the spreadsheet
4. Select a fresh worksheet in the workbook and go Data>Import External Data>New Database Query>Excel and select the saved file name from wherever it is located. You will probably be asked to add tables, and you want to add "data1" and "data2".
5. This should take you into MSQuery. In MSQuery, click on the SQL button below the menu buttons at top left.
6. Copy in the following SQL to the SQL dialog (amend the workbook name and path to whatever is appropriate):

Note the back quotes around the file name.
7. Click OK and go File>Return Data to Microsoft Excel

Thanks for the code...that's way over my head though to be honest I'm still learning to be efficient with VBA, this small part is part of a larger program actually which is why i need it to function this way.

Okay, I see why it's bringing both over. They are in fact two different cell values. The second 'bob' has trailing spaces, which it picks up. To fix this, get rid of your trailing spaces (space characters put at the end of a value in the cells, they appear blank).

An easy way to fix this is 1) run your own code on the values, or 2) get ASAP Utilities (www.asap-utilities.com) and it has a routine which will clear the leading/trailing spaces of a selection of cells. These lead to many problems. Clear those spaces and it runs just fine.

Also, I did not realize you did not have any headers, this would change ever so slightly (some rows changed from 2 to 1) and I added a couple lines of deleting columns to clean things up a bit. This works with your presented data (assuming you've cleared the trailing spaces)...




<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> wks3 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> Rng1 <SPAN style="color:#00007F">As</SPAN> Range, Rng2 <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> wks3 = Worksheets(3)<br>    <SPAN style="color:#00007F">Set</SPAN> Rng1 = Worksheets(1).Range("Range1")<br>    <SPAN style="color:#00007F">Set</SPAN> Rng2 = Worksheets(2).Range("Range2")<br>    wks3.Range("A:D").Delete<br>    wks3.Range("A1").Resize(Rng1.Rows.Count, Rng1.Columns.Count).Value = Rng1.Value<br>    wks3.Range("C1").Resize(Rng2.Rows.Count, Rng2.Columns.Count).Value = Rng2.Value<br>    wks3.Range("C1", wks3.Cells(wks3.Rows.Count, 4).End(xlUp)).Cut wks3.Cells(wks3.Rows.Count, 1).End(xlUp).Offset(1)<br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>    wks3.Range("C:D").Delete<br>    <SPAN style="color:#00007F">With</SPAN> wks3.Range("A1", wks3.Cells(wks3.Rows.Count, 2).End(xlUp))<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    wks3.Range("A1", wks3.Cells(wks3.Rows.Count, 2).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, copytorange:=wks3.Range("C1"), unique:=<SPAN style="color:#00007F">True</SPAN><br>    wks3.Range("A:B").Delete</FONT>



Let us know how it works.

YAY - Thanks for all your help, I removed the trailing spaces and it did exactly what I wanted to. I appreciate you sticking with me and helping me solve this issue.

This is a great forum.

Joe