Transform range of cells to one column or row

[mswoods1]

This is something I needed to do today and wondered if anyone had a more elegant solution:

I had a range of cells, which were in the shape of a box, like this:<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>
<table class="tableizer-table">
<tr><td>d</td><td>e</td><td>c</td><td>d</td></tr> <tr><td>i</td><td>f</td><td>g</td><td>b</td></tr> <tr><td>a</td><td>j</td><td>k</td><td>h</td></tr></table>
I needed to transform the cells into a column and sort them to look like this:<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>
<table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th></tr> <tr><td>a</td></tr> <tr><td>b</td></tr> <tr><td>c</td></tr> <tr><td>d</td></tr> <tr><td>e</td></tr> <tr><td>f</td></tr> <tr><td>g</td></tr> <tr><td>h</td></tr> <tr><td>i</td></tr></table>
The way I did it was to first transform the range into a column by using the INDEX function and manually putting in the row/number that I needed, like this: <style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>row</th><th>column</th><th>letter</th><th>formula</th></tr> <tr><td>1</td><td>1</td><td>d</td><td>=INDEX($A$1:$D$3,A6,B6)</td></tr> <tr><td>1</td><td>2</td><td>e</td><td>=INDEX($A$1:$D$3,A7,B7)</td></tr> <tr><td>1</td><td>3</td><td>c</td><td>=INDEX($A$1:$D$3,A8,B8)</td></tr> <tr><td>1</td><td>4</td><td>d</td><td>=INDEX($A$1:$D$3,A9,B9)</td></tr> <tr><td>2</td><td>1</td><td>i</td><td>=INDEX($A$1:$D$3,A10,B10)</td></tr></table>

Then I sorted this column after getting all of the letters into a column.

But... is there a more elegant solution?

 

[machopicho]

With VBA?

Sub test()
Dim elem() As Variant
nc = Cells(1, Columns.Count).End(xlToLeft).Column
nr = Cells(Rows.Count, 1).End(xlUp).Row
nt = nc * nr
r = 1
c = 1
ReDim elem(nt)
For i = 1 To nt
elem(i) = Cells(r, c)
c = c + 1
If c = nc Then
c = 1
r = r + 1
End If
Next
For rw = 1 To nt
Cells(rw, 5) = elem(rw)
Next
With Worksheets("[COLOR=red]NAME[/COLOR]").Sort '<--- change with your sheet's name
        .SetRange Range(Cells([COLOR=red]1[/COLOR], [COLOR=red]5[/COLOR]), Cells(nt, [COLOR=red]5[/COLOR])) '<-- change row, column where you want result
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
End Sub

 

[mirabeau]

Sub reorder()
Dim e, k As Long, c As Range
With Range("A1").CurrentRegion
For Each e In .Cells
    k = k + 1
    Cells(k + .Rows.Count + 2, 1) = "=" & e.Address
Next
Set c = Cells(.Rows.Count + 3, 1)
c.Resize(k).Sort c, 1
End With
End Sub