excel 用于比较两个工作表并删除行的代码

1wnzp6jl 于 2023-10-22 发布在其他

关注(0)|答案(1)|浏览(83)

我有这个代码完成，基本上所有它做的是比较两个不同的表在Excel中。它查看某些列，如果它们匹配，则进入下一步等。截至目前，代码需要大约一分钟才能完成，这是如果Excel没有崩溃后，它确实完成。所以我想知道是否有更好的方法来比较这两个表，而不是有激活命令，我使用，以便来回切换。有2200行，所以数据量很大。看看下面，让我知道是否有更好的方法来做到这一点。谢谢你，谢谢

For i = lastrow To 2 Step -1
    Worksheets("Sheet1").Activate
    cell1 = Cells(i, 2)
    Worksheets("Validations").Activate
For j = Blastrow To 2 Step -1
    cell2 = Cells(j, 2)
    If cell1 = cell2 Then
        Worksheets("Sheet1").Activate
        cell1 = Cells(i, "BY")
        Worksheets("Validations").Activate
        cell2 = Cells(j, "BY")
        
        
        If cell1 = cell2 Then
            Worksheets("Sheet1").Activate
            cell1 = Cells(i, "CD")
            Worksheets("Validations").Activate
            cell2 = Cells(j, "CD")
            
            
            If cell1 = cell2 Then
                Worksheets("Sheet1").Activate
                cell1 = Cells(i, "O")
                If IsEmpty(cell1) Then
                cell1 = Cells(i, "S")
                    If IsEmpty(cell1) Then
                        cell1 = Cells(i, "W")
                    End If
                End If
                Worksheets("Validations").Activate
                cell2 = Cells(j, "O")
                If IsEmpty(cell2) Then
                    cell2 = Cells(j, "S")
                    If IsEmpty(cell2) Then
                        cell1 = Cells(j, "W")
                    End If
                End If
                
                
                If cell1 = cell2 Then
                    Worksheets("Sheet1").Activate
                    cell1 = Cells(i, "Q")
                    If IsEmpty(cell1) Then
                        cell1 = Cells(i, "U")
                        If IsEmpty(cell1) Then
                            cell1 = Cells(i, "Y")
                        End If
                    End If
                    Worksheets("Validations").Activate
                    cell2 = Cells(j, "Q")
                    If IsEmpty(cell2) Then
                        cell2 = Cells(j, "U")
                        If IsEmpty(cell2) Then
                            cell1 = Cells(j, "Y")
                        End If
                    End If

                    If cell1 = cell2 Then
                        Worksheets("Sheet1").Activate
                        Cells(i, "CL") = "Approve"
                        Exit For
                    Else
                        Worksheets("Sheet1").Activate
                        Cells(i, "A").EntireRow.Delete
                        Exit For
                    End If
                Else
                    Worksheets("Sheet1").Activate
                    Cells(i, "A").EntireRow.Delete
                    Exit For
                End If
            Else
                Worksheets("Sheet1").Activate
                Cells(i, "A").EntireRow.Delete
                Exit For
            End If
        Else
            'delete row from sheet1
            Worksheets("Sheet1").Activate
            Cells(i, "A").EntireRow.Delete
            Exit For
        End If
        Exit For
    End If
    If j = 2 Then
        Worksheets("Sheet1").Activate
        Cells(i, "A").EntireRow.Delete
        Exit For
    End If
Next j
Next i
Worksheets("Validations").Delete

excel

来源：https://stackoverflow.com/questions/77212648/expediting-vba-code-for-comparing-two-sheets-and-deleting-rows

1条答案

按热度按时间

gg0vcinb1#

使用Activate不是必须的，实际上应该避免。
请参阅
如何避免在Excel VBA中使用Select
您的代码可以进行优化以提高效率，如下所示。请将相同的改进应用于其余部分。

Dim Sht1 As Worksheet, ShtV As Workbook
    Set Sht1 = Worksheets("Sheet1")
    Set ShtV = Worksheets("Validations")
    For i = lastrow To 2 Step -1
        cell1 = Sht1.Cells(i, 2)
        For j = Blastrow To 2 Step -1
            cell2 = ShtV.Cells(j, 2)
            If cell1 = cell2 Then
                cell1 = Sht1.Cells(i, "BY")
                cell2 = ShtV.Cells(j, "BY")
                If cell1 = cell2 Then
                    cell1 = Sht1.Cells(i, "CD")
                    cell2 = ShtV.Cells(j, "CD")
                    If cell1 = cell2 Then

嵌套的If可以简化如下。
注意：正如chris neilsen的评论中指出的那样，建议的代码更改并不构成效率改进。

Dim Sht1 As Worksheet, ShtV As Workbook
Set Sht1 = Worksheets("Sheet1")
Set ShtV = Worksheets("Validations")
For i = lastrow To 2 Step -1
    For j = Blastrow To 2 Step -1
        If Sht1.Cells(i, 2)=ShtV.Cells(j, 2) And _
            Sht1.Cells(i, "BY")=ShtV.Cells(j, "BY") And _
            Sht1.Cells(i, "CD")=ShtV.Cells(j, "CD") Then 
            ' Your code
        Else
            ' Your code
        End If
    Next j
Next i

赞(0）回复(0）举报 2023-10-22

我来回答

excel 用于比较两个工作表并删除行的代码

1条答案

相关问题

热门标签

最新问答