1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Deleting Rows that have duplicate data

Discussion in 'Business Applications' started by PincivMa, May 20, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hello Again

    This is an Excel question. I have many rows in an Excel spread sheet that have duplicate data. These duplicate rows vary in content and in length each and every day. So far I have been deleting the duplicate rows manually. This task is now becoming time consuning. To make it easier on myself, I do a sort so that duplicates are all clumped together. For example I may the following in Columns A and B. Note, there are no spaces between different products.

    000-00-001 product A
    000-00-001 product A
    .
    .
    .
    000-00-002 product B
    000-00-002 product B
    .
    .
    .
    etc and so on

    I tried writing a macro to eliminate the duplicates only but it does not work properly. Can you help me on this one?

    Mario
     
  2. xXLdev

    xXLdev

    Joined:
    May 17, 2004
    Messages:
    34
    This is really one of the features that Microsoft really should add to Excel. Everyone wants to do this. Here is a macro I wrote ages ago to do it.

    I hope this works for you.

    To use the macro select the area that has the data. The macro will then prompt you for the column letter that has the duplicate data. Then those rows will be deleted.

    Code:
    Sub RemoveDups()
        Dim rowStart As Integer, rowEnd As Integer, row As Integer
        Dim colStart As Integer, colend As Integer
        Dim colDup As Integer
        
        colDup = Asc(UCase(InputBox("Enter column letter that contains the duplicates"))) - Asc("A") + 1
        
        rowStart = Selection.row
        rowEnd = rowStart + Selection.Rows.Count - 1
        colStart = Selection.Column
        colend = colStart + Selection.Columns.Count - 1
        
        row = rowStart
        While row < rowEnd
            If (Cells(row, colDup).Value = Cells(row + 1, colDup).Value) Then
                Range(Cells(row, colStart), Cells(row, colend)).Delete Shift:=xlUp
                rowEnd = rowEnd - 1
           Else
                row = row + 1
           End If
        Wend
    End Sub
    
    Make sure you make a copy of your spreadsheet before testing this macro, since it deletes data.
     
  3. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    i used to import the data into access and then run a query which just copied the data from one table to another but I set the query to not allow duplicates. then re exported the data to excel.
     
  4. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Thanks Ldev

    The macro works just fine. I modified it a bit. I took out the input box and made the macro start in cell A3. I have also put in the a code that selected columns from A3 to F3 and down to the last entry. I works fine.

    When I first ran it with the input box, I only highlighted row A only, but I had data that belonged to row A in columns B to F. I noticed that only row A got rid of the duplicates and left them in the other columns. I had the impression that the enite row would be deleted. I was wrong. However, when all the columns with dupicate data were selected, then everything worked fine
     
  5. xXLdev

    xXLdev

    Joined:
    May 17, 2004
    Messages:
    34
    PincivMa,

    I am glad that you were able to modify the macro to work for you.

    The code does not delete rows but deletes cells and moves them up. So you needed to select all your data
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/230936

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice