Removing Unused Lines After Remove Duplicates

**freybe06** · 09-16-2013, 03:41 PM

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 Buttrey** · 09-16-2013, 03:48 PM

Hi,

When you say you're removing duplicates are you removing the rows that contain the duplicates or just the cell values. I'm implying from your post it's the latter.

One way would be to remove the rows as part of your remove duplicates routine.

Another would be to filter the range for non blank and use the

Please Login or Register  to view this content.

syntax.

Yet another would be to use Advanced Data Filter to copy the non blank rows to your destination area.

**Solus Rankin** · 09-16-2013, 03:48 PM

Can you attach your sample workbook?

**freybe06** · 09-16-2013, 04:20 PM

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.

**freybe06** · 09-16-2013, 04:42 PM

Update: It turns out its not actually the remove duplicates and copy/paste thats causing my issue - its when I try to put left and right edge borders in there. That's when my scroll bar goes from 3000+ cells to 60,000+ cells.

For some reason, using

Please Login or Register  to view this content.

will cause the issue, but using

Please Login or Register  to view this content.

does work.

Go figure...

Update to the update -

Apparently this works too:

Please Login or Register  to view this content.

I must be going nuts.

**Solus Rankin** · 09-16-2013, 04:51 PM

That's why I was hoping to get an example. It will show it all in context.

Removing Unused Lines After Remove Duplicates

LinkBack

Thread Tools

Rate This Thread

Display

Removing Unused Lines After Remove Duplicates

Re: Removing Unused Lines After Remove Duplicates

Re: Removing Unused Lines After Remove Duplicates

Re: Removing Unused Lines After Remove Duplicates

Re: Removing Unused Lines After Remove Duplicates

Re: Removing Unused Lines After Remove Duplicates

Thread Information

Users Browsing this Thread

Similar Threads

Remove or Highlight Duplicates only if all lines the same, if not change font color

[SOLVED] Remove duplicates without removing cells (leave blank)

[SOLVED] Remove Duplicates issue...Not Removing as it should

Comparing lines and removing duplicates

Remove Duplicates not removing all duplicate entries

Bookmarks

Bookmarks

Posting Permissions