时间戳

首页 > 解决方案 > VBA 代码仅适用于第一张纸

vba - VBA 代码仅适用于第一张纸

问题描述

我正在尝试编辑一个包含 17 张工作表的工作簿，每个工作簿在 A 列中都有城市列表，我想删除我在下面的代码中创建的数组中不等于值的行。此代码逐页工作，但一旦我尝试循环它，它就不起作用。

    Sub myDeleteRows()

Dim MyCol As String
Dim lRow As Long
Dim iCntr As Long
Dim i As Integer
Dim core_cities As Variant
Dim sh As Worksheet

core_cities = Array("Bristol", "Birmingham", "Cardiff", "Leeds", "Liverpool", "Manchester", "Newcastle-upon-Tyne", "Nottingham", "Sheffield")

lRow = 140

For Each sh In ActiveWorkbook.Sheets

For i = lRow To 4 Step -1

    If IsError(Application.Match(Range("A" & i).Value, core_cities, False)) Then
    Rows(i).Delete
    End If

Next i
Next sh

MsgBox ("complete")
End Sub

标签： vbaexcel

解决方案

当您遍历它们时，您必须参考每个工作表。

Sub myDeleteRows()

    Dim MyCol As String
    Dim lRow As Long
    Dim iCntr As Long
    Dim i As Integer
    Dim core_cities As Variant
    Dim sh As Worksheet

    core_cities = Array("Bristol", "Birmingham", "Cardiff", "Leeds", "Liverpool", "Manchester", "Newcastle-upon-Tyne", "Nottingham", "Sheffield")

    lRow = 140

    For Each sh In ActiveWorkbook.Sheets

        with sh
            For i = lRow To 4 Step -1

                If IsError(Application.Match(.Range("A" & i).Value, core_cities, False)) Then
                    .Rows(i).Delete
                End If

            Next i
        end with

    Next sh

    MsgBox ("complete")

End Sub

注意 .Range 和 .Rows 在 With ... End With 中时，而不是简单的 Range 或 Rows。前缀句点（例如.）提供对 sh 的关联父工作表引用。

推荐阅读