VBA遍历工作表以删除重复项 [英] VBA looping through sheets removing duplicates

问题描述

我见过类似的事情，但是我的代码似乎可以正常运行，我只想检查是否有改进或有哪些潜在错误，可能会导致意想不到的后果.

I have seen similar things, but my code that seems to be working I just want to check for improvements or what potential bugs, unintended consequences there could be for it.

我收到了一些电子表格，这些电子表格最终获得了重复的信息.有很多电子表格，每个文件中有100张纸.显然，不想使用删除重复的信息来手动浏览每张纸.经过四处搜寻，我认为我有一个解决方案，希望您对此有意见.我有JS背景，这是我第一次使用vba.

I have been handed spreadsheets that have ended up getting duplicate information. There are a lot of spreadsheets, some have 100 sheets inside each file. Obviously don't want to go through each sheet manually using remove duplicate information. After searching around I think I have a solution and want your opinions on it. I have a JS background and this is my first time using vba.

Sub RemoveDuplicates()

'RemoveDuplicates宏'选择所有值，然后删除重复项'

' RemoveDuplicates Macro ' Selects all values then removes duplicates '

 ' Declare Current as a worksheet object variable.
     Dim Current As Worksheet
     Dim starting_ws As Worksheet
     Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning

     ' Loop through all of the worksheets in the active workbook.
     For Each Current In Worksheets
        Current.Activate
        
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        ActiveSheet.Range("A1" & LastRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11), Header:=xlYes
  
     Next
     
starting_ws.Activate 'activate the worksheet that was originally active

结束子

我只在每张纸上查找重复项，而不在查找工作表.列数也将保持不变.非常感谢您的提前帮助.抱歉，这是基本的VB.

I am only looking for duplicates on each sheet, not compating worksheets. Also the number of columns will stay the same. Thank you very much for your help in advance. Sorry if this is a basic VB thing.

推荐答案

删除重复项(多列和工作表)

• 使用 Option Explicit .
• 此 ActiveSheet.Range("A1"& LastRow)肯定是错误的.考虑一下.
• 您必须限定 Range ， Cells ， Rows 和 Columns 的资格，例如 Current.Cells ， Current.Rows.Count ...
• 您不必激活 Current . Current 是您的情况下的 ActiveSheet (因为您已激活它)，因此无需使用 ActiveSheet .
• 不要让 Excel 定义范围:您要定义它.

Remove Duplicates (Multiple Columns and Worksheets)

• Use Option Explicit.
• This ActiveSheet.Range("A1" & LastRow) is surely wrong. Think about it.
• You have to qualify Range, Cells, Rows and Columns e.g. Current.Cells, Current.Rows.Count...
• You don't have to activate Current. Current is the ActiveSheet in your case (since you have activated it), so no need to use ActiveSheet instead.
• Do not let Excel to define the range: you define it.

有疑问的代码

Option Explicit

Sub removeDupes()
     
     ' Here's a question:
     '     Why can't I use 'Cols' instead of the array written explicitly?
     Dim Cols As Variant
     Cols = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
     
     Const FirstAddress As String = "A1"
     Dim wb As Workbook
     Set wb = ActiveWorkbook
     
     Dim ws As Worksheet
     Dim LastCell As Range
     
     For Each ws In wb.Worksheets
         Set LastCell = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(, 10)
         ws.Range(FirstAddress, LastCell).RemoveDuplicates _
             Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11), Header:=xlYes
         ' This doesn't work.
         'ws.Range(FirstAddress, LastCell).RemoveDuplicates _
             Columns:=Cols, Header:=xlYes

         ' Try this with ByVal array.
         'ws.Range(FirstAddress, LastCell).RemoveDuplicates _
             Columns:=(Cols), Header:=xlYes
     Next

End Sub

这篇关于VBA遍历工作表以删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！