Guinaba
Board Regular
- Joined
- Sep 19, 2018
- Messages
- 218
- Office Version
- 2016
- Platform
- Windows
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?
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?
VBA Code:
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