copy one row of named range spanning non contiguous columns

[Lucas12345678920]

Hello again,

So this one is a boggler. I am not even sure this is possible, but I am sure trying.

I have a named range that includes for example cells a4:d9, g4:j9, l4:m9, call it HST. I also have named ranges that cover each column group individually (one for a4:d9, one for g4:j9 and one for l4:m9) and one named range for one specific column in the range (I) -- call this one HSTNumbers) I have a VBA script that will loop through each row in named range HSTNumbers. For each value in HSTNumbers, I want to copy only that row from HST range.

The script to do this looks as follows

Sub SubmitHSTBalanceSheet()
    Dim MyArray1
    MyArray1 = Range(ActiveSheet.Names("HSTNumbers").RefersToRange.Address)
    For i = 1 To UBound(MyArray1)
        If MyArray1(i, 1) = vbNullString Or MyArray1(i, 1) = "" Then
        
        Else
                ThisWorkbook.Sheets("Sheet2").Range("HST").Rows(i).Copy
        End If
    Next i
End Sub

And it works. Sort of. It does copy some of the data from the correct row and correct range. However, the data is truncated where the columns were not contiguous. That is to say that it copies only cells on that row, in that range, but only those adjacent to the active cell.

so on the right row, in the right range but only copying cells within that column block... only the contiguous cells.. in this example only cells from that row in the g to j columns.

Is this a bug of named ranges? Or is there another method to do this with non contiguous ranges (unions)?

Any thoughts, comments, suggestions would be very appreciate.

Thanx

[Lucas12345678920]

Well, with no new suggestions, I simply broke the non contiguous ranges into groups of contiguous ranges.... works.. just seems sloppy... (and kinda defeats the purpose of having non contiguous named ranges)...

Even though I have butchered my script into submitting to my will, I am still hoping there is a better solution...

Anyone have one? Dont be shy...