use `union` with For Each loop without using array-CodePudding

on the below code. I select row on condition.
I need to use union to select all these rows with the below condition.
I know that my code can changed completely by using arrays,
but for a learning purpose, how union can be used to do the same action?
In advance grateful for useful comments and answer.

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

CodePudding user response：

Please, try the next adapted code. It is not necessary/good to create a huge Union range (from the whole rows). You can create it only from the specific cells matching condition and then Select/Delete/Copy its 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

Now, if you want copying such a Union range, it will also not be good to copy the whole row matching the specific condition. In such a case you can intersect the Union range with sheet UsedRange and copy only the intersection...