excel - 使用 `union` 和(For Each 循环)而不使用数组

Question

在下面的代码上。我根据条件选择行。
我需要使用 工会 选择具有以下条件的所有这些行。
我知道我的代码可以通过使用数组来完全改变，
但是出于学习目的，如何使用 union 来执行相同的操作？
提前感谢有用的意见和回答。

Sub Union_Test()

  Dim ws As Worksheet: Set ws = ActiveSheet
  Dim lastR As Long: lastR = ws.Cells(Rows.Count, 1).End(xlUp).Row
  Dim cel As Range, rng As Range, srg As Variant

Set rng = ws.Range("V3:V" & lastR)
    For Each cel In rng
     If cel.value = "Yes" Then
        cel.EntireRow.Select
     End If
 Next cel
 
     ' I need here to use union to select all (cel.EntireRow)
    
End Sub

Answer 1

请尝试下一个改编的代码。创建一个巨大的Union 没有必要/很好范围(从整行)。您只能从匹配条件的特定单元格中创建它，然后选择/删除/复制它的EntireRow :

Sub Union_Test()
  Dim ws As Worksheet: Set ws = ActiveSheet
  Dim lastR As Long: lastR = ws.cells(rows.count, 1).End(xlUp).row
  Dim cel As Range, rng As Range, uRng As Range

 Set rng = ws.Range("V3:V" & lastR)
    For Each cel In rng
     If cel.value = "Yes" Then
        If uRng Is Nothing Then
            Set uRng = cel
        Else
            Set uRng = Union(uRng, cel)
        End If
     End If
  Next cel
  If Not uRng Is Nothing Then uRng.EntireRow.Select
End Sub

现在，如果你想复制这样的 Union范围，复制匹配特定条件的整行也不好。在这种情况下，您可以与 Union 相交带表的范围 UsedRange并仅复制交叉点...