Creating a list from a matrix of data

Hi Emily,

Thank you for the forum question.


Please add the following code to a module in the workbook with the tubular table.

Option Explicit

Public Sub TableToList()
If ActiveCell.CurrentRegion.Rows.Count < 2 Then
Exit Sub
End If
If ActiveCell.CurrentRegion.Columns.Count < 2 Then
Exit Sub
End If

Dim table As Range
Dim rngColHead As Range
Dim rngRowHead As Range
Dim rngData As Range
Dim cel As Range

Dim rowVal As Variant
Dim colVal As Variant
Dim val As Variant

Set table = ActiveCell.CurrentRegion
Set rngColHead = table.Rows(1)
Set rngRowHead = table.Columns(1)
Set rngData = table.Offset(1, 1)
Set rngData = rngData.Resize(rngData.Rows.Count - 1, rngData.Columns.Count - 1)

ActiveWorkbook.Worksheets.Add

ActiveCell.Value = "Row#"
ActiveCell.Offset(0, 1).Value = "RowValue"
ActiveCell.Offset(0, 2).Value = "ColValue"
ActiveCell.Offset(0, 3).Value = "Data"
ActiveCell.Offset(1, 0).Select

Dim n As Long
For Each cel In rngData
colVal = rngColHead.Cells(cel.Column - table.Column + 1)
rowVal = rngRowHead.Cells(cel.Row - table.Row + 1)
n = n + 1
ActiveCell.Value = n
ActiveCell.Offset(0, 1).Value = rowVal
ActiveCell.Offset(0, 2).Value = colVal
ActiveCell.Offset(0, 3).Value = cel.Value
ActiveCell.Offset(1, 0).Select
Next
End Sub


You will need to select a cell inside your table and then run the macro. The macro will insert a new worksheet with the flat list created from your table.

Please let me know if you cannot get it to work.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector