+ Reply to Thread
Results 1 to 6 of 6

Removing Unused Lines After Remove Duplicates

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    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!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,829

    Re: Removing Unused Lines After Remove Duplicates

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Removing Unused Lines After Remove Duplicates

    Can you attach your sample workbook?
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  4. #4
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Removing Unused Lines After Remove Duplicates

    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.

  5. #5
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Removing Unused Lines After Remove Duplicates

    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.
    Last edited by freybe06; 09-16-2013 at 04:51 PM.

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Removing Unused Lines After Remove Duplicates

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 12
    Last Post: 09-10-2013, 09:45 PM
  2. [SOLVED] Remove duplicates without removing cells (leave blank)
    By RobertOHare in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2013, 09:57 AM
  3. [SOLVED] Remove Duplicates issue...Not Removing as it should
    By trosasco in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2013, 12:57 PM
  4. Comparing lines and removing duplicates
    By christya24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2009, 02:30 PM
  5. Remove Duplicates not removing all duplicate entries
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2008, 05:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1