Combine two ranges

[tiredofit]

This code combines two ranges.

It works if the ranges are on the same sheet but fails if they're not.

Dim rng1 As Range
  
    Set rng1 = Sheet1.Range("A1:A3")
  
    Dim rng2 As Range

    Set rng2 = Sheet2.Range("A1:A3")

    Dim rng3 As Range

    Set rng3 = Union(rng1, rng2)

    Dim rng3element As Range

    For Each rng3element In rng3

        Debug.Print rng3element.Value

    Next rng3element

How can I amend it so it would work?

Thanks

 

[fadee2]

you can not use union for ranges lying on different worksheets.
Have a look at Range union in different sheet

 

[tiredofit]

you can not use union for ranges lying on different worksheets.
Have a look at Range union in different sheet

Thanks

 

[fadee2]

Your welcome.

Just a question though, what exactly were you trying to achieve with that??

 

[tiredofit]

Your welcome.

Just a question though, what exactly were you trying to achieve with that??

I'm trying to check to see if a particular value exists in either range 1 or range 2.

I'm doing it the long way using arrays instead.

I'm creating three arrays and adding values from the ranges.

 

[dmt32]

I'm trying to check to see if a particular value exists in either range 1 or range 2.

I'm doing it the long way using arrays instead.

I'm creating three arrays and adding values from the ranges.

try a workaround from the union method and see if it will do what you want?

   Dim arrRng As Variant
   Dim rng(1 To 2) As Range
    Dim cell As Range

    Set rng(1) = Sheet1.Range("A1:A3")

    Set rng(2) = Sheet2.Range("B1:B3")


    For Each arrRng In Array(rng(1), rng(2))
        For Each cell In arrRng.Cells
            MsgBox cell.Parent.Name & " " & cell.Address
        Next cell
    Next arrRng

Dave

 

[tiredofit]

try a workaround from the union method and see if it will do what you want?

   Dim arrRng As Variant
   Dim rng(1 To 2) As Range
    Dim cell As Range

    Set rng(1) = Sheet1.Range("A1:A3")

    Set rng(2) = Sheet2.Range("B1:B3")


    For Each arrRng In Array(rng(1), rng(2))
        For Each cell In arrRng.Cells
            MsgBox cell.Parent.Name & " " & cell.Address
        Next cell
    Next arrRng

Dave

Not quite. I would need to adapt your code for my needs.

If Sheet1 contains the values 1, 2, 3 and Sheet2 contains, 4, 5, 6, I want the output to be in a single column, 1,2,3,4,5,6.

However, I've already done it using arrays.

Thanks for your help though.

 

[dmt32]

Not quite. I would need to adapt your code for my needs.

If Sheet1 contains the values 1, 2, 3 and Sheet2 contains, 4, 5, 6, I want the output to be in a single column, 1,2,3,4,5,6.

However, I've already done it using arrays.

Thanks for your help though.

without seeing your full code it was just a guess - but good you have it resolved.

Dave

 

[fadee2]

@tiredofit care to share your complete set of resulting code?

 

[tiredofit]

@tiredofit care to share your complete set of resulting code?

Dim Array1() As Variant
    
    Array1() = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Dim Array1Rows As Integer
    
    Array1Rows = UBound(Array1(), 1)
    
    Dim Array2() As Variant
    
    Array2() = Sheet2.Cells(1, 1).CurrentRegion.Value
    
    Dim Array2Rows As Integer
    
    Array2Rows = UBound(Array2(), 1)
    
    Dim CombinedArray() As Variant
    
    ReDim CombinedArray(1 To Array1Rows + Array2Rows, 1 To 1) As Variant
    
    Dim Counter As Integer
    
    For Counter = 1 To Array1Rows
    
        CombinedArray(Counter, 1) = Array1(Counter, 1)
        
    Next Counter
    
    For Counter = 1 To Array2Rows
    
        CombinedArray(Array1Rows + Counter, 1) = Array2(Counter, 1)
        
    Next Counter
    
    Sheet1.Cells(10, 10).Resize(Array1Rows + Array2Rows, 1).Value = CombinedArray()