Removing Unused Lines After Remove Duplicates

Hello Everyone,

I have a macro that removes duplicates for 2 columns and then pastes those 2 columns to a new worksheet. I am having an issue with the copy/paste part of it. Excel keeps copying all the way down to the last row before the remove duplicates. For example, if column A and B had 10 rows, then I did a remove duplicates and it got rid of half of them, the copy/paste would still give me 5 rows with data and 5 blank rows.

At a small level like that, it doesn't matter, but when I have thousands of lines of data and the scroll bar goes down way too far, then it becomes a pain.

Does anyone know how to write code so it only picks up the data thats left after a remove duplicates?

Thanks!

Richard and Solus,

Thank you for your responses. The code I am using is:

Please Login or Register  to view this content.

This should do the same thing as going to the Data Ribbon and using the "Remove Duplicates" button.


Solus - If you open a spreadsheet and fill in column A1 through 10,000 with "1" in each cell, then use the "Remove Duplicates" button, you'll see the scroll bar will stay the same size as though you have 10,000 filled cells. That is essentially my issue. Excel still recognizes those cells.