☰

删除多张工作表中的重复项

tag-icon • tag-icon microsoft-excel vba
删除多张工作表中的重复项 
Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count
          ActiveWorkbook.Worksheets(I).Range("A4:Z1000").RemoveDuplicates Columns:=1, Header:=xlYes
         Next I

End Sub

我尝试运行上述代码来删除多个 Excel 工作表中 A 列的重复项，但是得到以下结果：

运行时错误 1004 应用程序定义或对象定义的错误

有人能解释一下我的代码出了什么问题吗？

答案1

您遇到的错误可能是由于移除重复项不支持多范围。如果有效，请尝试以下操作，

Sub WorksheetLoop()
    Dim WS_Count As Integer
    Dim I As Integer
    Dim ws As Worksheet
    Dim rng As Range
    Dim col As Range

    WS_Count = ActiveWorkbook.Worksheets.Count

    For I = 1 To WS_Count
        Set ws = ActiveWorkbook.Worksheets(I)
        Set rng = ws.Range("A4:Z1000")

        If Not rng Is Nothing Then
            For Each col In rng.Columns
                If Application.CountA(col) > 1 Then
                    col.RemoveDuplicates Columns:=1, Header:=xlYes
                End If
            Next col
        End If
    Next I
End Sub

相关内容