The Multiplex Class is very powerful, fast, flexible and lightweight - it exposes Methods to manipulate Jagged Arrays in memory. The download features 79 Examples demonstrating how you can use the Class Methods - you can take parts of the Class if you wish to reduce overhead in your Project, just take the bits you need

Required:
Excel 2010 or Excel 2013 (32bit or 64bit). Some knowledge of VBA to Copy & Paste Code into respective Code Modules in your own Projects

Features include:
Methods to Add Ranges, Add Table Ranges, Add Formula Ranges, Overlap Ranges, Sort by Rows or Columns, Sort Column Order, Sort Column Order & Insert Blank Columns, Union contiguous and non-contiguous Ranges, search for Headers and dynamically pick up the Range, perform Regular Expressions on data, Delete Rows or Columns, Purge Rows or Columns, Read & Write data including CSV data, Compress or Expand Rows or Columns, use the Application.WorksheetFunction on array slices and otput large volumes of array data en masse from a specific Cell. This Class also allows you to create an array of Multiplex's or Multiplex slices. You can also populate a ComboBox or ListBox

Getting Started:
Download the File and run some of the Examples in the Code Module 'mdlExamples'. To use the Class simply export and import the 'Multiplex.cls' File into your own Projects or Copy & Paste the Code in the 'Multiplex' Class into a Class Module of the same name 'Multiples' in your own Project

Declaring a Multiplex Array avoiding Auto-instancing:
Let's start by instantiating a class into an object. We will call our Array "Thingy" and avoid auto-instancing using a small piece of Code like this (auto-instancing: adding any overhead to Code where in program execution VBA will testing the Variable for Nothing every time it is encountered in the Code and may automatically create instances of the variable):

' //  instantiate a class into an object
'     we will avoid auto-instancing
Dim Thingy As Multiplex
Set Thingy = New Multiplex
 
Here is an example of adding a Worksheet Range to the Multiplex, picking up all data and stopping at a Blank Cell using xlDown:
Thingy.AddRangeXLDown Sheet1, Range("B11")
 
To output the contents of a Multiplex Array you can use the OutputRange() Method requiring a single Cell like this:
Thingy.OutputRange Range("N3")
 

Declaring an Array of Multiplex's using Auto-instancing:
You can also create an Array of Multiplex's without instantiating a class into an object and store different lengths of the Worksheet data like this:
' // create an array of 3 Multiplex's using auto-instancing
ReDim Thingy(1 To 3) As New Multiplex

' // add data to each Slice and you can use them independently
Thingy(1).AddRange Range("B12:C15")
Thingy(2).AddRangeXLDown Sheet1, Range("B11")
Thingy(3).AddRange Range("G13:K13")
 

More Advanced Method - UnionMultipleRangesByColumns():
Of course the above Examples are just scratching the surface of what this Class allows you to do. Say you wanted to Union contiguous and non-contiguous multiple jagged Ranges passed as a ParamArray into a Variant array joined by Columns then you could use the UnionMultipleRangesByColumns() Method:
Dim Thingy As Multiplex
Set Thingy = New Multiplex

Thingy.UnionMultipleRangesByColumns Range("B12:C13,B15:D15,F2"), Range("E12:E15"), Range("B18:D19,G18:I18")

Thingy.OutputRange Range("N3")
 

More Advanced Method - Transmogrify():
What if we want to splice one Array by another Array? Well you can use the Transmogrify() Method. This Example will transmogrify an existing Multiplex by another splicing the two Arrays starting at 2 Rows by 2 Columns. The Array will become a completely new sized Array, being 'transmogrified' - transformed in a surprising or magical manner:
Dim Thingy As Multiplex
Set Thingy = New Multiplex

' // add a Range
Thingy.AddRange Range("B12:C14")

' // transmogrify the existing Multiplex (the Range picked up above) by another Range starting at 2 Rows by 2 Columns
Thingy.Transmogrify Range("B23:D23"), 2, 2

Thingy.OutputRange Range("N3")
 
Use the link above to download or view the Multiplex Array Class. You can also Share this article to Twitter, Facebook or Google+ using the links at the bottom of this page