How to create a list from a table

[Joaquim]

I have a 2 x 2 table:
C D
A 1 2
B 3 4

I would like to tranform it into a list as follows:
Row Column Value
A C 1
A D 2
B C 3
B D 4
How do I do this in Excel. Of course my tables are much bigger than this and
they are not Pivot Tables.
thanks
Joaquim

 

[Jim Thomlinson]

Select your entire table and run this code...

Sub test()
Dim rng As Range
Dim rngToSearch As Range
Dim rngSelection As Range
Dim wksNew As Worksheet
Dim rngPaste As Range

Set rngSelection = Selection
With rngSelection
Set rngToSearch = Range(.Cells(2, 2), .Cells(.Cells.Count))
End With

Set wksNew = Worksheets.Add
Set rngPaste = wksNew.Range("A1")
For Each rng In rngToSearch
rngPaste.Value = Intersect(rng.EntireRow, _
rngSelection.Columns(1).EntireColumn)
rngPaste.Offset(0, 1).Value = Intersect(rng.EntireColumn, _
rngSelection.Rows(1).EntireRow)
rngPaste.Offset(0, 2).Value = rng.Value
Set rngPaste = rngPaste.Offset(1, 0)
Next rng
End Sub