dynamic rows: cell value concatenation

[deepika :excel help]

In sheet1 x denotes rows which is dynamic and starts from 6 . so we have
D6,D7,D8... etc E6,E7.. etc
under D6 i have ABC and under E6 i have XYZ .
i have to concatenate the text in D6 with text in E6 (result shud be like
ABC_XYZ) and put it into a column B3 of sheet2 in same work book
here the rows in sheet 1 are are dynamic. so whenever an entry happens in
sheet 1 , the text in the two columns should be concatenated and put into
sheet 2 column.
so wat VBA code do i write to concatenate and copy into sheet2

 

[Binary Bandit]

Deepika

I don't write VB so can't tell you what the code would be, so here is a
rough and ready way of doing it (and it will depend upon what you are using
Sheet 2 for as to whether this will be useful or not). In cell B3 of Sheet 2
I would type the following formula
=IF(ISBLANK(Sheet1!D6),IF(ISBLANK(Sheet1!E6),"",Sheet1!D6&"_"&Sheet1!E6),Sheet1!D6&"_"&Sheet1!E6)
I would then copy that formula down the column for as many rows as you think
you might get data in Sheet 1.
What the formula does is to insert a blank value in the result cell in Sheet
2 if BOTH cells D6 and E6 are empty in Sheet 1. If either of them has a
value then it will populate the result cell in Sheet 2 with the result of
cells D6 and E6 concatenated with am underscore between the cell values.
And, of course, copying it down the column will only show data if
corresponding rows in Sheet 1 are populated. Quick and dirty and no VB
code!!!