Consulting

Results 1 to 6 of 6

Thread: Delete duplicate rows if they do not contain a specific word

  1. #1
    VBAX Regular
    Joined
    Oct 2014
    Posts
    95
    Location

    Delete duplicate rows if they do not contain a specific word

    Please could you help with code that will delete duplicate rows in a worksheet, except those with the word NAME in cell A1.

    Thanks.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,618
    Location
    With ActiveSheet
    If .Range("A1") <> "NAME" Then
    
    For each Row
    If Row = Duplicate Then
    Row.Delete
    End If
    Next
    End If
    End With
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Oct 2014
    Posts
    95
    Location
    Quote Originally Posted by SamT View Post
    With ActiveSheet
    If .Range("A1") <> "NAME" Then
    
    For each Row
    If Row = Duplicate Then
    Row.Delete
    End If
    Next
    End If
    End With
    Thanks SamT. Unfortunately, I am getting "Compile error: syntax error" at

    For each Row
    and "variable not defined" for Duplicate.

    I have tried to fix it thus:

    With ActiveSheet
    
    Dim Row As Variant
    Dim Duplicate As Variant
    
        If .Range("A1") <> "NAME" Then
             
            For Each Row In .Range
                If Row = Duplicate Then
                    Row.Delete
                End If
            Next
        End If
    End With
    but this has resulted in "Run Time error 450 Wrong number of arguments or invalid property assignment".

    Please could you help fix this?

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,618
    Location
    Please could you help with code that will delete duplicate rows in a worksheet, except those with the word NAME in cell A1.
    Sub t()
    If Range("A1") <> "NAME" Then WorkSheet.Delete
    End Sub

    Maybe if you had a more detailed explanation and possible a attachment with some sample Data and an example of what you want the results to look like, we could develop some code.

    Your profile says that you have been here for 2 years with many posts, so you should be quite familiar with Excel terminology, VBAX, and coding requirements.

    Recommended reading
    https://www.amazon.com/Excel-Dummies.../dp/1568840500
    https://www.amazon.com/VBA-Dummies-J.../dp/0470046503
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Oct 2014
    Posts
    95
    Location

    Sample attached plus clearer explanation of request

    Thanks SamT. I apologise that I wasn't clear.

    I attach a sample workbook which contains:

    1) Worksheet "Original input" with 93 rows of random text (there's no significance in the 93 - it's random)
    2) Worksheet "Desired output" which I created manually to show the result once the macro has operated.

    The macro will:

    a) Delete, from "Original input", all rows where all columns are duplicated except those rows with NAME in column A.
    b) Keep the rows in the same order.
    c) Keep any row with NAME in column A, regardless of whether the remaining columns have duplicated cells.
    d) Overwrite the worksheet "Original input" with the result. I just want to keep the end product, not the raw data.

    I hope that's easier to work with. I'm very grateful to you and the other experts on the forum for your help.

    Dummy.xlsx

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,618
    Location
    That works.

    I have to go to town, but here's my (cryptic) design notes.

    'Go down the Rows until Cells(1) is empty
    'If Cells(rw, 1) <> "Name" then
    'Columns (1).Find(Cell 1, before A1)
    'If Found then
    ' If Test_for_ Dupe(Rw, Found.Row) then delete Found Row
    'Rw = R + 1
    'loop


    'Test_for_Dupe Tests
    'If CountA's <> = then false
    'If CountA = 1 then True
    'Assign to arrays
    'If UBounds <> = then False
    'Else
    'Check each index for <> = then False

    'All tests passed = true
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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