Build alternate cell range vba

Question

my intent of building a dynamic alternate cells range as failed, obviously for lack of knowledge.

For j = 1 To numFrame
    Set MaxStRange_OD_OUT = ActiveSheet.Range(Cells(1, 2 * (j + 1)))
    MaxVal = Application.Max(MaxStRange_OD_OUT)
    Debug.Print MaxVal
Next j

what I would like to achive is

MaxStRange_OD_OUT = ActiveSheet.Range(Cells(1, 4),Cells(1, 6),Cells(1, 8))

of course the number of cells can vary let say from 1 to 10 (numFrame)

thanks

Answer 1

You could use Application.Union to build up a range.

Sub UnionRangeExample()

Dim rng As Range
Dim i As Long

    For i = 1 To 20 Step 2

    If rng Is Nothing Then

    Set rng = ActiveSheet.Cells(i, 1)

    Else

    Set rng = Application.Union(rng, ActiveSheet.Cells(i, 1))
    End If

    Next i

MsgBox rng.Address

End Sub

---

In your situation you can use it like this:

Sub UnionRangeExample_v2()

Dim rng As Range
Dim j As Long
Dim numFrame As Long


numFrame = 10


    For j = 1 To numFrame
        If rng Is Nothing Then

        Set rng = ActiveSheet.Cells(1, 2 * (j + 1))

        Else

        Set rng = Application.Union(rng, ActiveSheet.Cells(1, 2 * (j + 1)))
        End If
    Next j

MsgBox "max for: " & rng.Address & " is " & Application.Max(rng)

End Sub