Variable Block Length Column To Table
This page describes code for spliting apart a column of data into
a table when the blocks in the source column have a variable number of elements.
On the Column To Table page we looked at formulas that can be
used to create a two-dimensional table from a column of data. This works fine as long as each
block of data in the column has a fixed number of elements. However, it is often the case that
the blocks of data in the source column are variable length. Some blocks may have 3 elements
while other blocks have 5 elements. The code here will create a two dimensional table
each column of which is one block of value from the source column data.
For the purposes at hand, we will assume that there is some type of delimiting value at the beginning of
each data block in the source column that indicates where one block begins following
the elements of the previous block. This may be a constant string, an empty cell, or a string
that can be evaluated with the Like operator. There may be any number
of cells (including empty cells) between two delimited cells. It is possible also that two delimiter
values occur in adjacent cells with no data between them. A sample source column is shown below.
In this image, the delimiter cells are colored in red. This is for illustration only. In practice,
any cell formatting is irrelevant and is ignored (and thus the cell formatting will not
appear in the result table. The delimiter cells contain the text NameN,
where N is some arbitrary number. Because the code uses the
Like operator, all of these values will be recognized as a block
delimiter. The delimiter would be specified as NAME*, where the
* indicates a match of zero or more characters. See the VBA help file
for the Like operator for a full discussion of available formats used with Like.
If the delimiter is not a blank cell, each delimiter is included in the first row of the results
table. If the delimiter is a blank cell, these blank cells do not appear in the results table.
The code for the VarColToTable procedure which creates the two dimensional
table from the source column is shown below. You will need to change the
values annotated by to match the requirements of your workbook and data.
Dim R As Range
Dim Dest As Range
Dim DR As Range
Dim LastRow As Long
Dim SourceWS As Worksheet
Dim DestinationWS As Worksheet
Const C_DELIMITER = "NAME*"
Const C_DEST_FIRST_CELL = "E1"
Set SourceWS = Worksheets("Sheet1")
Set DestinationWS = Worksheets("Sheet1") '<<< CHANGE
Set R = SourceWS.Range("A1")
LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row
If Not UCase(R) Like UCase(C_DELIMITER) Then
MsgBox "The first item in the list must conform to C_DELIMITER '" + C_DELIMITER & "'."
Do Until R.Row > LastRow
If UCase(R.Text) Like C_DELIMITER Then
If Dest Is Nothing Then
Set Dest = DestinationWS.Range(C_DEST_FIRST_CELL)
If C_DELIMITER = vbNullString Then
Set Dest = Dest(0, 1)
Set Dest = Dest(1, 2)
Set DR = Dest
DR.Value = R.Text
Set DR = DR(2, 1)
Set R = R(2, 1)
The following show the resulting table created by the code:
||This page last updated: 18-December-2008.