+ Reply to Thread
Results 1 to 12 of 12

Deleting Row Duplicates

  1. #1
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Deleting Row Duplicates

    I have attached an extract of a worksheet where I import a csv file find the duplicate bits of information based on 5 cell values form sheet Temp5 and return True or False in column AI of sheet1. The position of the data is where it is found on its respective sheets.

    The assistance I am looking for is to delete the duplicates (red blocks of data) on sheet Temp5 and move the cells left. The data will always start from row 4 and the maximum would be 100

    Any assistance would be greatly appreciated. Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Deleting Row Duplicates

    This deletes the duplicates in each row. It doesn't use sheet 1 or conditional formatting. It finds the duplicates on its own.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Re: Deleting Row Duplicates

    AlphaFrog you are definitely on my Christmas list - works perfectly. Thanks

  4. #4
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    863

    Re: Deleting Row Duplicates

    just for diversity, I'll put my contribution here also:
    Please Login or Register  to view this content.
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  5. #5
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Deleting Row Duplicates

    Alpha/Someone,

    Could you please explain how the .Resize method works in your code?

    Please Login or Register  to view this content.
    ...I feel like I've been missing out on some good .Resize opportunities.
    <---If my answer helped, please click *

  6. #6
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Deleting Row Duplicates

    Bulina2k,

    Does this line: cel.Offset(, 5 * (i - 1)).Resize(, 5).Copy Range("A" & down) transpose sets of 5 cells all in one row to sets of 5 cells all in one column (so the data goes from:
    v1 w1 x1 y1 z1, v2 w2 x2 y2 z2, ... vN, ..., zN (all in row 4)
    -to-
    v1 w1 x1 y1 z1
    v2 w2 x2 y2 z2
    .
    .
    .
    vN ... zN

    ?

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Deleting Row Duplicates

    Joe,
    While learning VBA, resize was the most difficult concept to understand and spent hours in frustration.
    The way you should think is " One way stretching". And always starts from 1, while offset can go both ways(Negative for back ward) and starts from 0.

    Let say you have range (cells(I,1),cells(I,5)).Select where I is the row number(1). You are selecting row1 1, column 1, to row 1 column 5. You are going 5 to the right from column 1. You can do the same with resize.
    Cells(I,1).resize(,5).select. You are stretching 5 to the right from column 1, in other words, takes you from column A to column E. The stretch (resize) is 5. So you have a starting point and go to right or below, choosing a number to stretch.
    I hope this helps.

  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    863

    Re: Deleting Row Duplicates

    Quote Originally Posted by joe31623 View Post
    Bulina2k,

    Does this line: cel.Offset(, 5 * (i - 1)).Resize(, 5).Copy Range("A" & down) transpose sets of 5 cells all in one row to sets of 5 cells all in one column (so the data goes from:
    v1 w1 x1 y1 z1, v2 w2 x2 y2 z2, ... vN, ..., zN (all in row 4)
    -to-
    v1 w1 x1 y1 z1
    v2 w2 x2 y2 z2
    .
    .
    .
    vN ... zN

    ?
    Right!
    I did wanted to take advantage of the Range.RemoveDuplicates function so I need it to put them one under the other (5 per set).
    Of course, after the removal I need it to put them back.

  9. #9
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Re: Deleting Row Duplicates

    AlphaFrog - As outline in the personal note I sent, I have a little problem with how the code has run in my actual file. It deletes when there is a single duplicate. It appears to work fine when it was in column A but when I copied the code over to my file I get this problem. I have attached another file of sample data and the macro enabling you to review. As for the Greatful; I'm just a poor speller. Again thanks
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Deleting Row Duplicates

    This starts at column U to test for duplicates.

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Re: Deleting Row Duplicates

    AlphaFrog - Again many thanks - I have tested and it works a treat

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Deleting Row Duplicates

    You're welcome.

+ 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. [SOLVED] Deleting Duplicates!!
    By melkon99 in forum Excel General
    Replies: 11
    Last Post: 10-01-2012, 08:06 PM
  2. deleting duplicates
    By crosset in forum Excel General
    Replies: 1
    Last Post: 06-02-2011, 07:48 PM
  3. Deleting Duplicates-find out a way to delete duplicates
    By sighlent1 in forum Excel General
    Replies: 2
    Last Post: 04-19-2011, 10:17 AM
  4. Deleting both duplicates
    By kblinkin in forum Excel General
    Replies: 9
    Last Post: 11-11-2010, 06:26 PM
  5. Deleting Duplicates
    By 3smees23 in forum Excel General
    Replies: 3
    Last Post: 05-07-2009, 10:48 AM
  6. [SOLVED] Deleting Duplicates
    By dcost@sovereignbank in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-26-2005, 10:05 PM
  7. [SOLVED] Deleting the first row of two duplicates.
    By Georgyneedshelp in forum Excel General
    Replies: 2
    Last Post: 10-19-2005, 12:05 PM
  8. [SOLVED] Deleting duplicates
    By Judd Jones in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2005, 12:06 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