none
Need help figuring out how to delete single records, not dupilicate ones, using VBA RRS feed

  • Question

  • Hi Everyone,

    So my main issue is that I can't figure out where to start here. I have this list, which is being exported from a sharepoint list.

    The list contains a column called report id. The thing is, I need to keep any rows that are a duplicate record. So if something like "This Item" appears twice in the list, then I need to keep it, otherwise, if it only shows up once, then I need to delete it.

    I have figured out how to get the items into an array, which I thought would help, the idea being that I could set the first item in the array to a variable, then compare it to all the other values in the list, if one exists, then bang, keep both of these. I have had so many issues with this that I don't even know where to start.

    Any ideas at all would be great.


    Best regards, Mike

    Thursday, December 1, 2016 4:30 PM

All replies

  • Hi,

    Not very efficient code though.. but still you will be able to manage your task using this code:

    Before running the code, change the Range as per your need. Make sure that you are providing the range only for the column where you are trying to find the duplicate values.

    Important:

    Make a back up of your data before running this code because this involves deletion of records and once data lost by macro, can not be recovered.

    Sub keepDuplicates()
    
        Dim rangeOfIDs As Range
        Dim rowsToBeDeletedString As String
        Dim rowsToBeDeleted As Variant
        
        Set rangeOfIDs = Range("C2:C13") ' kindly change this Range as per your need
        'Note above range should be only for one column where your IDs are
    
        For Each Rng In rangeOfIDs
           rangeOfIDs.Select
           With Selection
            .Find (Rng.Value)
           If .FindNext(Rng).Address = Rng.Address Then
            ' Store the row number to be deleted in a string
             rowsToBeDeletedString = rowsToBeDeletedString & ";" & (Rng.Row)
            End If
           End With
        Next
        'Remove the first delimeter
        rowsToBeDeletedString = Mid(rowsToBeDeletedString, 2, Len(rowsToBeDeletedString))
        'Store all the row number in an array
        rowsToBeDeleted = VBA.Split(rowsToBeDeletedString, ";")
    
        ' Now delete all the rows which were supposed to be deleted
        For irow = UBound(rowsToBeDeleted) To 0 Step -1
            Rows(rowsToBeDeleted(irow)).Delete
        Next
    End Sub


    Vish Mishra

    Friday, December 2, 2016 12:40 PM
  • Create a Helper Column...Use  COUNTIF on Report ID...If duplicate then COUNTIF will return >1, if single then 1.

    ....Filter the "1".....delete it.

    Probably manual delete will not take much time.

    Later you can try VBA.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Friday, December 2, 2016 12:43 PM
    Answerer