删除重复行，每行长度为 40 列。该怎么办？

发布于 2024-12-03 20:19:20 字数 862 浏览 0 评论 0原文

好吧，这不是我第一次在这里问这些问题，但我无法弥补。我必须删除列中的重复行。说

range("A1:A30000") have to delete all the duplicate rows in these columns

我已经使用了程序员在 stackoverflow 上给出的先前问题中的先前解决方案。它们工作得很棒，但这次不行。上次我每行有 15 列，但这次每行有 40 列，并且没有一个脚本可以工作。电脑刚刚挂起，有时需要 2 小时，这不是我想要的。我已经使用了 Issun、Jon、Reafidy 和 Doc Brown 建议的字典方法，即使它们不起作用。我不知道为什么。

所以我想使用高级过滤器，但我无法从 vba 中删除重复的行。我在谷歌上没有找到它，我看到了带有方框的手册，但没有看到 vba 脚本，即使我发现它不能正常工作。

Range("A1:A5").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

应用该行后我必须做什么。现在删除重复的行。我记得它就像复制粘贴，但我不太记得代码，就像

xltypeinvisble:delete

我不太记得代码一样。我记得我在谷歌上读过，现在我找不到它了。

谁能告诉我使用高级过滤方法删除重复条目？您是否认为每行有 40 列，这使得我的脚本需要很长时间并且有时会挂起？可能是什么原因造成的？

任何其他更快的方法都值得赞赏（可能是字典方法）。我相信人们说字典方法，但我不知道它不能正常工作可能是代码错误或不确定原因。它只是挂断了。所以不知道为什么，对于我测试的小数据来说它工作得很好。但不适合更大的。

非常感谢任何帮助！

原文

Well this is not the first time im asking these question here but I cannot make it over.
I have to delete the duplicate rows in a column. say from

range("A1:A30000") have to delete all the duplicate rows in these columns

I have used the previous solutions from my previous questions given by the programmers on the stackoverflow. They are working amazing but not these time.Last time I had each row of 15 columns but these time each row with 40 columns and none of the scripts working. The pc its just getting hang and sometimes taking 2 hrs thats not what i want. I have used the dictionary method as suggested by Issun,Jon,Reafidy and Doc brown even they are not working. I dont know why.

So I thought to use the advanced filters but im unable to delete the duplicate rows from vba. I dont find it on google, I see the manual one with the boxes but not the vba script even i find its not working fine.

Range("A1:A5").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

What I have to do after applying that line. delete the duplicate rows now. I remember its like copy pasting but i dont remember the code well and something like

xltypeinvisble:delete

I dont remember the code well . I remember that i read on google now im unable to find it.

can anyone tell me to delete the duplicate entries using the advanced filter method ? do you think ,that rows had 40 columns each and that made my scripts to take long time and sometimes hang up?what might be the cause to it?

any other method which is faster is appreciated (probably dictionary one). I belive people say dictionary method but i dont know its not working fine may be the code error or not sure about the reason.its just getting hang up. so unable to know why, well its working fine for small data that i tested. but not with the bigger one.

any help is greatly appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泪痕残 2024-12-10 20:19:20

我不熟悉您正在寻找的一次性删除所有不可见行的单行命令，但您可以循环遍历范围内的行并手动删除它们。

如果这对您来说运行速度仍然太慢，您可能需要考虑添加 VBA 命令以在宏运行时关闭屏幕更新和重新计算。

Sub DeleteDUpes()
    Dim ThisRange As Range
    Dim NewRange As Range

    Set ThisRange = Range("A1:A30000")
    ThisRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    ' Loop through each row from bottom up, deleting rows hidden by filter
    For thisrow = ThisRange.Rows.Count To 1 Step -1
        Set NewRange = ThisRange.Resize(1).Offset(thisrow - 1, 0)
        If NewRange.EntireRow.Hidden Then
            r.EntireRow.Delete
        End If
    Next
End Sub

I'm not familiar with the single line command you are looking for to delete all invisible rows at once, but you can loop through the rows in the range and manually delete them.

If this still runs too slow for you, you may want to consider adding the VBA commands to turn off screen updating and re-calculations while the macro is running.

Sub DeleteDUpes()
    Dim ThisRange As Range
    Dim NewRange As Range

    Set ThisRange = Range("A1:A30000")
    ThisRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    ' Loop through each row from bottom up, deleting rows hidden by filter
    For thisrow = ThisRange.Rows.Count To 1 Step -1
        Set NewRange = ThisRange.Resize(1).Offset(thisrow - 1, 0)
        If NewRange.EntireRow.Hidden Then
            r.EntireRow.Delete
        End If
    Next
End Sub

回复收藏 0 原文

~没有更多了~