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

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this do what you want?

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))
  End With
End Sub

 

[Guinaba]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this do what you want?

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))
  End With
End Sub

Hi @Peter_SSs,

I've updated my details, thanks for letting me know. How can I write back the final range to sheet to check whether is ok? Any place in the sheet just to check.

Cheers,

 

[Peter_SSs]

Thanks for updating your profile.

Try something like this. Results go into columns BA:BL

If this is the data you want and your ultimate goal is to put the values in a worksheet range, then that can be done directly, without even populating the array first.

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

 

[Guinaba]

Hi @Peter_SSs,

That is great! Should we use paste special in the formula to keep the same format as before when pasting the array?

Before:

After:

 

[Peter_SSs]

It is unclear what you are really trying to do.

The cell formatting is not included if the values are put into an array so no type of paste from an array will include the original cell formatting.

If your goal is to copy those 4 ranges** to a single range, including formatting, then you would not need to use an array at all. It could be something as simple as this:

Sub CopyRangesToOne()
    Range("E5:E18,O5:P18,X5:Y18,AG5:AM18").Copy Destination:=Range("CA1")
End Sub

** Note though that I have changed all the ranges to start at row 5
If there was something in, say, P5 that you did not want in the destination it would probably be simplest to copy it too with the code above but then remove it from the results.

 

[Guinaba]

It is unclear what you are really trying to do.

The cell formatting is not included if the values are put into an array so no type of paste from an array will include the original cell formatting.

If your goal is to copy those 4 ranges** to a single range, including formatting, then you would not need to use an array at all. It could be something as simple as this:

Sub CopyRangesToOne()
    Range("E5:E18,O5:P18,X5:Y18,AG5:AM18").Copy Destination:=Range("CA1")
End Sub

** Note though that I have changed all the ranges to start at row 5
If there was something in, say, P5 that you did not want in the destination it would probably be simplest to copy it too with the code above but then remove it from the results.

Thanks @Peter_SSs
That is correct, my goal is to copy those 4 ranges to a single one, including format. Firstly,I tried that using Union but without success.

I tried your new code but keep getting the following error:

Run-time error '1004':

We can't do that to a merged cell.

 

[Peter_SSs]

I tried your new code but keep getting the following error:

I can see that you have merged cells in the source range and I tested that scenario without problems.
Do you have merged cells in the destination range?
If so, could they be removed?
What is your actual destination range?

 

[Guinaba]

I can see that you have merged cells in the source range and I tested that scenario without problems.
Do you have merged cells in the destination range?
If so, could they be removed?
What is your actual destination range?

Thanks @Peter_SSs! Much appreciate it! I've just removed the merged cells...all good now.

 

[Peter_SSs]

You're welcome. Glad it is sorted.

BTW, I have changed the post marked as the Solution. You had marked post #8 but that one doesn't really answer the question whereas I think #6 is the one isn't it?