Hi, I need to do this, i have a table like this
1 2 3 4
a c d
9 7 5

What I need to do is:
1
2
3
4
a
c
d
9
7
5

So a list comes from a table with data, as you have noticed, on the table there are spaces, that is because when I want the list to be filled, i want it to skip the spaces... Thanks!

Hi,

This code may help you.

Sub Transposing()
Dim Table 'Table variable
Dim Target 'Target cell
Dim i As Integer, k As Integer, j As Integer

Table = Application.InputBox("Enter data", Type:=64)
Set Target = Application.InputBox("Enter target", Type:=8)
j = 0
For i = 1 To UBound(Table, 1)
    For k = 1 To UBound(Table, 2)
        If Table(i, k) <> "" Then
            Target.Cells.Offset(j, 0) = Table(i, k)
            j = j + 1
        End If
    Next k
Next
End Sub

-vemaju

Thanks for the code! but can you explain it? Thanks...

Hi,

Here is the same code commented
 Posted - 04/06/2006 : 3:34:14 PM


--------------------------------------------------------------------------------

Hi,

This code may help you.

Sub Transposing()
Dim Table 'Table variable
Dim Target 'Target cell
Dim i As Integer, k As Integer, j As Integer


'*** Gets values to an table array (like excel table)
Table = Application.InputBox("Enter data", Type:=64)
'*** Gets target cell from user
Set Target = Application.InputBox("Enter target", Type:=8)
j = 0
'*** Looping through table array. Ubound 1 is the Upper Bound of first dimension
For i = 1 To UBound(Table, 1)
'*** Looping through table array. Ubound 2 is the Upper Bound of second dimension
    For k = 1 To UBound(Table, 2)
    '*** tests if there is a value
        If Table(i, k) <> "" Then
            '*** writes value to a cell
            Target.Cells.Offset(j, 0) = Table(i, k)
            '*** counter variable for cell offset from target cell
            j = j + 1
        End If
    Next k
Next
End Sub

Hope this helps you to understand how the code works.
You can run the code step by step (F8) and follow the variables in Locals Window View-Locals Windows in editor



-vemaju