Loop through cells and add to union(range)

Hi,

You missed a Set

        If Not newRng Is Nothing Then
            Set newRng = Union(newRng, Range(cell.Address))
        Else

When you have more than 1 column in range that you are looping, you should specify .Cells object of range.

For Each cell in rng.Cells

Also since you declared newRng as range object, you should Set newRng instead of just newRng = xxxx.

So your code becomes...

Sub test()
Dim newRng As Range
Dim rng As Range
Dim cell As Range

Set rng = Range("A1:C6")

For Each cell In rng.Cells
    cell.Select
    If cell.Value = 6 Then
        If Not newRng Is Nothing Then
            Set newRng = Union(newRng, Range(cell.Address))
        Else
            Set newRng = Range(cell.Address)
        End If
    End If
Next cell

newRng.Select

End Sub