Union of range not working - Needed for an array

[jxb]

to all

breaking down some code to small bits for testing. The following is just a dummy code which aim to replicate a more complex vba code

I want to UNION some ranges but it it not recognised - a syntax issue I guess can someone point me in the right direction?
There will be a follow question (already working on it) regarding the most efficient of searching for a number in column zero on my array while looping through a range

Dim ifirstrow As Integer, ilastrow As Integer

ifirstrow = 1
ilastrow = 5
Dim rg0 As Range, rg1 As Range, rg2 As Range, rg3 As Range
Dim rgfull As Range

Dim wksheet1 As Worksheet: Set wksheet1 = Worksheets("Sheet1")
Dim wksheet2 As Worksheet: Set wksheet2 = Worksheets("Sheet2")
Dim wksheet3 As Worksheet: Set wksheet3 = Worksheets("Sheet3")

With Application.ActiveWorkbook

    With wksheet1   'Worksheets("Sheet1")
        Set rg0 = .Range(.Cells(ifirstrow, 1), .Cells(ilastrow, 1))
        Set rg1 = .Range(.Cells(ifirstrow, 2), .Cells(ilastrow, 4))
    End With
 
    With wksheet2   'Worksheets("Sheet1")
        Set rg2 = .Range(.Cells(ifirstrow, 2), .Cells(ilastrow, 4))
    End With
    
    With wksheet3   'Worksheets("Sheet3")
        Set rg3 = .Range(.Cells(ifirstrow, 2), .Cells(ilastrow, 4))
    End With
    
End With

Set rgfull = Union(rg1, rg2, rg3)

Dim myarray As Variant
myarray = rgfull.Value

 

[6StringJazzer]

A Range object is restricted to one sheet. Your Union call is referring to ranges on three sheets.

 

[jxb]

Thanks . I see. So what is the best way of taking what is essentially 3 “tables” of data and put them in an array? Tables are of the same size. In me real ble I could more than 3 sheets
1 Loop through the sheets and “augment” the array? Would need re-sizing I guess
2 Create 3 arrays and merge them? Don’t think there is such capability with array. Have not looked into that for a little no time. Maybe use a vba math module/function?

 

[6StringJazzer]

What are you ultimately going to do with that data once you load it?

 

[jxb]

A few manipulations of the data may be required later but at the moment 2 possibles ideas are being looked at;

1 a simple copy-paste of the union ranges. I could loop over n-times, get the range of interest, then paste it to the target sheet. And then do other things.

2 I’ll need to do a search of specific value in the 1st column of the union range. In another post , I was corrected and using .Find() was suggested. Should do what I want. Then if value found extract entire row of data

Just thought that created a union range might be better/easier than having to loop over N sheets. The example/test is set with 3. If I have all the data in 1 “range” (in my head it’s like a table) then it might be easier. Maybe not ...

Thanks
John