Generating list from column of cells with multiple lines

Question

I've got a table and one of its column is kind of attributes of item. One item can have many attributes, and all of them are stored in one cell per each item, in seperate lines of text. I want to make script that enlist all the attribute types I've used so far and save that list in the second sheet. It propably cannot be achieved by standard formulas, so I assume the need to use VBA. I never used VBA before and don't know how to do this task. Also I can't find any reference of this language. I'm using MS Office 2007.

Answer 1

Sub export()
Dim cell As Range
Dim i As Long, j As Long
Dim var() As String
j = 1
For Each cell In Sheets(1).Range("a1:a10")
    Var = Split(cell, vbLf)
        For i = 0 To UBound(Var)
            Sheets(2).Range("a" & j).Value = Var(i)
            j = j + 1
        Next i    
Next cell
End Sub

My example loops all the cells in the range a1:a10 of sheet1. Adapt it to your needs. Each cell is splitted on linefeed and each row copied in the second sheet.