excel - 有没有办法循环满足条件的行以设置单个范围

Question

我希望 VBA 循环遍历工作表中的行并根据满足条件的行设置范围。

目前我的代码只选择条件为真的第一行，我希望所有满足条件的行都包含在设置的范围内......

Sub setrng()
    Set WkSht = Sheets("Main")
    For i = 1 To 335
        If WkSht.Cells(i, 8).Value = "Y" And WkSht.Cells(i, 9).Value = "ZC" And WkSht.Cells(i, 10).Value = "N" Then

            Dim rng As Range
            With Application.Intersect(WkSht.Rows(i), WkSht.UsedRange)
                Set rng(i) = WkSht.Range(WkSht.Cells(i, 3), .Cells(.Cells.Count))
            End With

            If rng Is Nothing Then
                Exit Sub
            End If
        End If
    Next

标签： excelvba

Answer 1

最好使用 Union 来从范围内构建范围。一个小问题是你不能把什么都没有联合起来。在这种情况下（这是第一次迭代），您必须将收集的范围设置为第一个找到的范围。之后，使用联合

Sub setrng()
    Dim CollectedRange As Range
    Dim AddedRange As Range

    Set WkSht = Sheets("Main")

    For i = 1 To 335
        If WkSht.Cells(i, 8).Value = "Y" And WkSht.Cells(i, 9).Value = "ZC" And WkSht.Cells(i, 10).Value = "N" Then


            With Application.Intersect(WkSht.Rows(i), WkSht.UsedRange)
                Set AddedRange = WkSht.Range(WkSht.Cells(i, 3), .Cells(.Cells.Count))
            End With

            If Not AddedRange Is Nothing Then
                If CollectedRange Is Nothing Then
                    Set CollectedRange = AddedRange
                Else
                    Set CollectedRange = Union(CollectedRange, AddedRange)
                End If
            End If
        End If

    Next
    Debug.Print CollectedRange.Address
End Sub