Hi,

We get a lot of information that comes into different columns, say Product
1 - 88. We never know how many values there are in every colum or how many
Products there are

Now we want a macro that puts all the values into one column, say A - can be
in sheet 2. We do not want empty rows or columns but it is very important
that the figures are in the original order.

Regards

More information is needed. Is there a header row? If so, what do you
want to become of it? Might the be empty cells within the table? How
do you recognize how many columns are used, or is the first row always
filled for each column? If one of the products uses 17 rows, will all
of the products use 17 rows? What do you mean by "the original order"
when you are combining multiple columns into one colum; would
a b c
d e f
g h i

become
a
b
c
d
...
or
a
d
g
b
...

Jerry

Prixton wrote:

Hi,

We get a lot of information that comes into different columns, say Product
1 - 88. We never know how many values there are in every colum or how many
Products there are

Now we want a macro that puts all the values into one column, say A - can be
in sheet 2. We do not want empty rows or columns but it is very important
that the figures are in the original order.

Regards

1. enter the following small udf:

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function

2. click on column A and Insert Column this will result in a new un-used
column
3. click on the new A1 and enter =concat(B1:IV1)
4. copy down as far as necessary


finally locate and delete any empty rows
--
Gary''s Student


"Prixton" wrote:

Hi,

We get a lot of information that comes into different columns, say Product
1 - 88. We never know how many values there are in every colum or how many
Products there are

Now we want a macro that puts all the values into one column, say A - can be
in sheet 2. We do not want empty rows or columns but it is very important
that the figures are in the original order.

Regards

Hi,
Yes there is a header row but we are not interrested in getting the header
into the new column.
All posts from col A should be followed by B, C, D etc. The columns have
different number of values, there are no empty cells within a column .
We want the result to be
a
b
c
d
e
f
g

Regards


"Jerry W. Lewis" skrev i meddelandet
...
More information is needed. Is there a header row? If so, what do you
want to become of it? Might the be empty cells within the table? How do
you recognize how many columns are used, or is the first row always filled
for each column? If one of the products uses 17 rows, will all of the
products use 17 rows? What do you mean by "the original order" when you
are combining multiple columns into one colum; would
a b c
d e f
g h i

become
a
b
c
d
...
or
a
d
g
b
...

Jerry

Prixton wrote:

Hi,

We get a lot of information that comes into different columns, say
Product 1 - 88. We never know how many values there are in every colum or
how many Products there are

Now we want a macro that puts all the values into one column, say A - can
be in sheet 2. We do not want empty rows or columns but it is very
important that the figures are in the original order.

Regards