Difficult concatenate No 2

[stakar]

After the first concatenate i want to concatenate all the rows under th
checked boxes!

Be more specific

i have 20 cells in 800 rows

A B C D .... AA <-- columns
X X X ... <-- cells used as check box
Z Z Z ... Z <-- cells used as headers
1 3 4 5 ... N <-- cells with values

THIS =concatenate("A3";"B3";"D3") happens using the following cod
written by
Frank Kabel
------------------------------------

sub foo()
dim rng as range
dim cell as range
dim ret_str
set rng = range ("A3:AA3")
for each cell in rng
if cell.offset(-2,0).value = "X" then
ret_str = ret_str & cell.value
end if
next
range("A4").value = ret_str
end sub

----------------------------------------------------
Now , I want this to happen for all the next 800 rows

=concatenate("A4";"B4";"D4")
=concatenate("A5";"B5";"D5")
=concatenate("A6";"B5";"D6")

 

[Frank Kabel]

Hi
try the following (will put the concatenated value in column AB for
each row

sub foo()
dim rng as range
dim row_index as long
dim col_index as integer
dim ret_str

set rng = range ("A3:AA800")
for row_index = rng.row to rng.rows.count + rng.row - 1
ret_str = ""
for col_index = rng.column to rng.columns.count + rng.column - 1
if cells(1,col_index).value = "X" then
ret_str = ret_str & cells(row_index,col_index).value
end if
next
cells(row_index,"AB").value = ret_str
next
end sub

 

[stakar]

*Hi
try the following (will put the concatenated value in column AB for
each row

sub foo()
dim rng as range
dim row_index as long
dim col_index as integer
dim ret_str

set rng = range ("A3:AA800")
for row_index = rng.row to rng.rows.count + rng.row - 1
ret_str = ""
for col_index = rng.column to rng.columns.count + rng.column - 1
if cells(1,col_index).value = "X" then
ret_str = ret_str & cells(row_index,col_index).value
end if
next
cells(row_index,"AB").value = ret_str
next
end sub

Frank
thanks,
But its extremely, extremely slow.

I had in mind if u could create the function for the first row.
If i had the function "=concatenate("A3";"B3";"D3"....etc) " create
in the row AA1 for example, i could use a macro to fill all the wa
down the others cells.

Thanks one more
******************
Stathis
Greece - Patra

 

[Frank Kabel]

Hi
a function wouldn't be much faster. But you may give the following a
try
- download the free add-in Morefunc.xll (http://longre.free.fr/english)
and install it (it includes the function MCONCAT)
- now insert the following ARRAY formula in AB3 (entered with
CTRL+SHIFT+ENTER):
=MCONCAT(IF($A$1:$AA$1="X",A3:AA3,""))
and copy this down for all your rows

 

[stakar]

Thats all i wanted to do!!

Frank, you were too much helpful to me!!
Thanks a lot !!


See you around !!

Stathis
***********************
Patras - Greec

 

[Frank Kabel]

Hi
thanks for the feedback, good that this works for you