需要帮助找出如何使用VBA删除单个记录，而不是删除单个记录 [英] Need help figuring out how to delete single records, not dupilicate ones, using VBA

问题描述

大家好，

所以我的主要问题是我无法弄清楚从哪里开始。我有这个列表，它从sharepoint列表中导出。

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.

该列表包含一个名为report id的列。问题是，我需要保留任何重复记录的行。所以，如果像"This Item"这样的话。在列表中出现两次，然后我需要保留它，否则，如果它只显示一次，那么我需要
来删除它。

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.

任何想法都会非常好。

祝你好运，迈克

推荐答案

尽管代码效率不是很高..但是你仍然可以使用这段代码来管理你的任务：

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.

重要提示：

在运行此代码之前备份数据，因为这涉及删除记录，一旦宏丢失数据，就无法恢复。

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

这篇关于需要帮助找出如何使用VBA删除单个记录，而不是删除单个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！