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
定义范围:您要定义它. - Use
Option Explicit
. - This
ActiveSheet.Range("A1" & LastRow)
is surely wrong. Think about it. - You have to qualify
Range
,Cells
,Rows
andColumns
e.g.Current.Cells
,Current.Rows.Count
... - You don't have to activate
Current
.Current
is theActiveSheet
in your case (since you have activated it), so no need to useActiveSheet
instead. - Do not let
Excel
to define the range: you define it.
Remove Duplicates (Multiple Columns and Worksheets)
有疑问的代码
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屋!