Multiplex v7 · fast, flexible & lightweight, the Multiplex Class exposes Methods to manipulate Jagged Arrays in memory

This Class allows you to manipulate Jagged Arrays in memory

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

Download the File

Some of the Examples use a UserForm and require data to work. For the complete Class and Examples:
Download Multiplex Version 7

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). We will pick up all of the data in a Worksheet from Cell "A1" until we find a Blank Cell and then output it again at Cell "B1":

' //  instantiate a class into an object
'     we will avoid auto-instancing
Dim Thingy As Multiplex
Set Thingy = New Multiplex

Thingy.AddRangeXLDown Sheet1, Range("A1")
Thingy.OutputRange Range("B1")

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

' // Union all of the disperate Ranges
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")