Need a VBA to combine two excel files

[nickobob456]

I'm new to VBA so I was hoping someone could help me with my problem. GIVEN:

First excel file has X number of columns with Y rows

A B C D E (columns)
1 2 3 4 5 (row1)
6 7 8 9 10 (row2)

and 2nd excel has

A B C D (columns)
1 2 3 4 (row1)
6 7 8 9 (row2)
a b c d (row 3)
e f g h (row4)

trying to combine so it becomes
A B C D E (columns)
1 2 3 4 5 (row1)
6 7 8 9 10 (row2)
a b c d (row 3)
e f g h (row4)

need it to loop combine for over 20 columns and 500 rows

[Bernie Deitrick]

You don't need to loop. You could paste the second sheet's data block below the first sheet's, so that you have:

A B C D E (columns)
1 2 3 4 5 (row1)
6 7 8 9 10 (row2)
1 2 3 4 (row3)
6 7 8 9 (row4)
a b c d (row5)
e f g h (row6)

then select the first four columns (based on your example)and use Data / Remove Duplicates , and you will get:

A B C D E (columns)
1 2 3 4 5 (row1)
6 7 8 9 10 (row2)
a b c d (row3)
e f g h (row4)

[nickobob456]

Hi, Thanks for the fast response, I was wondering what if I had it like this:

A B C D E (columns)
1 2 X X 5 (row1)
6 7 8 9 10 (row2)
1 2 3 4 (row3)
6 7 8 9 (row4)
a b c d (row5)
e f g h (row6)

And I wanted to merge while Keeping the "X X" changed values the same. I tried to use the duplicate and only highlight the other columns but I noticed that everything shifted up.

A B C D E (columns)
1 2 X X 5 (row1)
6 7 8 9 10 (row2)
a b c d (row3)
e f g h (row4)

Thanks!

[Bernie Deitrick]

Sorry, my instructions weren't clear.

Use Data / Remove Duplicates (on the entire data set), but only select the columns that indicate duplicates - for your first example, that was A, B, C, and D. For your second example, that would be A and B.