Gathering Data into a List

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Lets say that I have a group of cells ten columns by three
rows with some of the cells containing text. Which cells
have text and which are blank will change every now and
then.

I want to create a column that gathers all data from the
above group of cells and puts them into a list. Blank
cells will not be displayed and the column will gather the
data for the first cell from A1, the second from A2, the
third from A3, the fourth from B1, etc... unless of course
the referenced cell is blank, in which case it would
gather the next bit of text.

How do I do this?
 
Here is the way to do it:

First, name your ranges - for example, "DataSource" and "List" (I assume you
know how to do so - select your table and press F3) Then write visual basic
procedure:

Sub CreateList (DataSource as range, List as range)
' DataSource: range with your data,
' List - a cell where your list starts
'Cursor defines the next position in the list where to put data

Dim Cell as range
Dim Cursor as range

'set cursor to the first cell in the list
Set Cursor=List

For Each Cell in DataSource
If Cell <> Empty then
Cursor.Value=Cell.Value
Set Cursor=Cursor.Offset(1,0)
End if
Next Cell
End sub

How to use:

You can put a button on your spreadsheet and assign a macro to it:
Public Sub UpdateList
CreateList Range("DataSource"), Range("List")
End Sub

Or, if you know how to do this, you can use an event procedure "on change"
for the sheet with your data to update your list automatically.

Best -
RADO
 
RADO
A small correction to your code. As written, your code produces a cell
sequence of Row - Column whereas the OP specified a cell sequence of
Column - Row. The following produces the requested sequence:
Sub ReArrange()
Dim c As Long
Dim r As Long
Dim Dest As Range
[A1].Select
Set Dest = [K1]
For c = 1 To 10
For r = 1 To 3
If Cells(r, c) <> Empty Then
Cells(r, c).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next
Next
End Sub

I assumed the data starts in A1 and the list starts in K1.
HTH Otto
 
Back
Top