How to combine multiple non-adjacent ranges in one?

[Guinaba]

Hi guys,

I have these 4 ranges that I'd like to combine in one, I've tried union but didn't worked. So I've found this function that in theory does the job, however not sure how to write the final range back to the sheet. Any suggestions?

Sub CombininingArrays()

Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range

  Set Rng1 = Sheets("Summary").Range("e5:e18")
  Set Rng2 = Sheets("Summary").Range("o6:p18")
  Set Rng3 = Sheets("Summary").Range("x6:y18")
  Set Rng4 = Sheets("Summary").Range("Ag5:am18")
 
  Dim CombinedRange As Collection
  Set CombinedRange = New Collection
  Dim Vartable() As Variant
   
  CombinedRange.Add Rng1
  CombinedRange.Add Rng2
  CombinedRange.Add Rng3
  CombinedRange.Add Rng4
 
  'transfer combinedRange to array using function CollectionToArray
   Vartable = CollectionToArray(CombinedRange)
 
End Sub

 Function CollectionToArray(col As Collection) As Variant()
    Dim arr() As Variant, index As Long, it As Variant
    ReDim arr(col.Count - 1) As Variant
    For Each it In col
        arr(index) = it
        index = index + 1
    Next it
    CollectionToArray = arr
 End Function

 

[Guinaba]

Hi @Peter_SSs ,

Do you mind asking how did you find this solution using the array and index function? I really liked it. The only limitation I see on that, is that all ranges needs to be within the gap 5:18, correct?

Sub CombineRangesToArray()
Dim Vartable As Variant
 
    With Sheets("Summary")
    Vartable = Application.Index(Cells, Evaluate("row(5:18)"), Array(5, 15, 16, 24, 25, 33, 34, 35, 36, 37, 38, 39))
    .Range("BA1").Resize(UBound(Vartable, 1), UBound(Vartable, 2)).Value = Vartable
    End With

End Sub

cheers,
Gill

 

[Peter_SSs]

Do you mind asking how did you find this solution using the array and index function?

This technique of loading multiple disjoint ranges into an array I came across in the forum many years ago

The only limitation I see on that, is that all ranges needs to be within the gap 5:18, correct?

The dimensions of an array are uniform so the rows/columns feeding into it also need to be uniform if they are all being done at once. If you wanted some cell values omitted as it appears from your original code then you would need to process the ranges separately or else put everything in at once per code like mine and then remove the unwanted values.

 

[Guinaba]

This technique of loading multiple disjoint ranges into an array I came across in the forum many years ago


The dimensions of an array are uniform so the rows/columns feeding into it also need to be uniform if they are all being done at once. If you wanted some cell values omitted as it appears from your original code then you would need to process the ranges separately or else put everything in at once per code like mine and then remove the unwanted values.

Thanks one more time @Peter_SSs! Wow! You got that solution from 2011! Time is flying...hehe