Save dynamic ranges into Global array and return combined ranges

Does it need to be Global ?

Global myArray

Sub test()
    With Range("e1").CurrentRegion
        myArray = Application.Index(Evaluate("if(" & .Address & "<>0," & .Address & ","""")"), _
            Evaluate("row(2:" & .Rows.Count & ")"), [{1,2,3,4,5,7,8,9}])
    End With
    With [ac3].CurrentRegion
        .Offset(2).ClearContents
        .Rows(3).Resize(UBound(myArray, 1), UBound(myArray, 2)) = myArray
    End With
End Sub

Hi jindon

Thanks for your reply. The code works, however, the output data gets overrided when I use the code for the second time.

Im not sure it needs to be Global. Maybe I did not explain myself correctly.
What I mean is that the populated cells in the array needs to stay everytime I use the sub-routine and be resized to fit the next range I wanna save. And only when I am ready to output, the saved ranges are returned to column AC:AJ.

As explained, I would like to filter on my data using cell A4 and A6. Once filtered, I would like to save my range. This will be done multiple of times.


Hope this makes sence.

Do you want it directly to output?

Option Explicit

Global myArray

Sub test_Your_Formula()
    Dim x As Long
    With Sheets("sheet3")
        myArray = .[FILTER($O$2:$W$47761,($U$2:$U$47761 > 0)*($U$2:$U$47761 < $A$6),"No Combo")]
        With .Range("ac" & Rows.Count).End(xlUp)(2)
            If IsArray(myArray) Then
                On Error Resume Next
                x = UBound(myArray, 2)
                On Error GoTo 0
                If x = 0 Then
                    .Resize(, UBound(myArray) - 1) = Application.Index(myArray, [{1,2,3,4,5,7,8,9}])
                Else
                    .Resize(UBound(myArray), UBound(myArray, 2)) = Application.Index(myArray, _
                        Evaluate("row(1:" & UBound(myArray) & ")"), [{1,2,3,4,5,7,8,9}])
                End If
            Else
                .Value = myArray
            End If
        End With
    End With
End Sub


Sub testHow_I_Do()
    Dim x
    With Sheets("sheet3")
        With .Range("o1").CurrentRegion
            x = .Columns(7).Address
            x = Filter(.Parent.Evaluate("transpose(if((" & x & ">0)*(" & x & "<a6),row(" & x & ")))"), False, 0)
            If UBound(x) > -1 Then myArray = Application.Index(.Value, Application.Transpose(x), [{1,2,3,4,5,7,8,9}])
        End With
        With .Range("ac" & Rows.Count).End(xlUp)(2)
            If UBound(x) > -1 Then
                If UBound(x) > 0 Then
                    .Resize(UBound(myArray, 1), UBound(myArray, 2)) = myArray
                Else
                    .Resize(, UBound(myArray)) = myArray
                End If
            Else
                .Value = "No Combo"
            End If
        End With
    End With
End Sub

Thanks, jindon. This is really good stuff. Both subroutines are working.

Have a nice weekend!