Macro - Moving Rows into columns

[quaser15]

I have attached my problem, It could be fruitful if any one can solve it. I will appreciate it.

http://imthiyaz.weebly.com/query.html

File name: imthiyaz.xls

In this problem I need Column A2 Joints With Column A8, subsequently i have data which is listed until A30000 lines, I need the macro code, which can do this job easily.

Thanks!

 

[Peter_SSs]

Hi Peter,

Thanks for providing this great learning

No problem.

I could be wrong but I don't think that is what the OP is asking for. However, if the task was to combine all the cell values into a single merged cell, here is a more compact way.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Arrange_Data_Merge()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> FirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 1 <SPAN style="color:#007F00">'<- First row of actual data</SPAN><br>    <br>    LastRow = Range("A" & Rows.Count).End(xlUp).Row<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> r = FirstRow <SPAN style="color:#00007F">To</SPAN> LastRow <SPAN style="color:#00007F">Step</SPAN> 9<br>        <SPAN style="color:#00007F">With</SPAN> Cells(r, 2).Resize(9)<br>            .Cells(1, 1).Value = Join(Application.Transpose(.Offset(, -1).Value), Chr(10))<br>            .Merge<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Columns("B").ColumnWidth = 38<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[quaser15]

Hi Peter

The concept you have understood is right, but my objective is merging the data from A1 and A8 into B1, since my information confined to name: A.Mohammed Imthiyaz in A1 and Company: PP Services in A8 and so on.. In order to seperate different addresses listed in column A, I have inserted ***************************************** line in end address .Hope you will correlate the explanation with the previous post. I appreciate you for your concern towards me.

Thanks!

 

[quaser15]

Hai Kunal

Thank you very much for analyzing my problem. The code is looking good and I have executed it. But it leads to blank data, couldn't figure out the merging.

Thanks again

 

[Peter_SSs]

Hi Peter

The concept you have understood is right, but my objective is merging the data from A1 and A8 into B1, since my information confined to name: A.Mohammed Imthiyaz in A1 and Company: PP Services in A8 and so on.. In order to seperate different addresses listed in column A, I have inserted ***************************************** line in end address .Hope you will correlate the explanation with the previous post. I appreciate you for your concern towards me.

Thanks!

My previous post was not trying to answer your question, but to suggest a coding hint to kunal_shrivastava. My suggestion for your problem was posted in post #9.

Did you try that code?

If it did not give the result you expected, please explain in what way it failed.

Here is my screen shot with original data in column A and the result of running the code in column B. That may help you to give any explanation. It may also show you the benefit of posting small screen shots directly here as I suggested early in your thread.

Excel Workbook
	A	B
1	Mohammed Imthiyaz	Mohammed Imthiyaz PP Services
2	L-91, Housing Unit
3	Erode
4	Chennai
5	Tamil Nadu
6	PH:42596628
7	FAX:638952
8	PP Services
9	*****************************************
10	Joseph Charles	Joseph Charles ABT Services
11	L-91, Housing Unit
12	Erode
13	Chennai
14	Tamil Nadu
15	PH:42596628
16	FAX:638952
17	ABT Services
18	*****************************************
19	Jack Pinto	Jack Pinto JAK Services
20	L-91, Housing Unit
21	Erode
22	Chennai
23	Tamil Nadu
24	PH:42596628
25	FAX:638952
26	JAK Services
27	*****************************************
Join data (2)

 

[Peter_SSs]

BTW, are they real people's names addresses phone numbers etc? If so, they may not be too happy to know such information has been posted publicly so please let me know so that I can replace them with dummy data.

 

[quaser15]

Hi Peter

As per your instruction i have executed the code you have sent on post 9# and this is the result that i got in merging of entire cells from A1 to A8 in column B, but my idea is to get the data merged only with A1 and A8 in column B (i.e Mohammed Imthiyaz PP Services) which you have done screen shot in previous post.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 215px"><COL style="WIDTH: 271px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Mohammed Imthiyaz</TD><TD rowSpan=9>Mohammed Imthiyaz
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
PP Services
*****************************************
</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>L-91, Housing Unit</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Erode</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Chennai</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Tamil Nadu</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>PH:42596628</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>FAX:638952</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>PP Services</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>*****************************************</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Joseph Charles</TD><TD rowSpan=9>Joseph Charles
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
ABT Services
*****************************************
</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>L-91, Housing Unit</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Erode</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Chennai</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>Tamil Nadu</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>PH:42596628</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>FAX:638952</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>ABT Services</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>*****************************************</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Jack Pinto</TD><TD rowSpan=9>Jack Pinto
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
JAK Services
*****************************************
</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>L-91, Housing Unit</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>Erode</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>Chennai</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>Tamil Nadu</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>PH:42596628</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD>FAX:638952</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD>JAK Services</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD>*****************************************</TD></TR></TBODY></TABLE>

My Required data is given below: (This is my needed output)

Join data (2)

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 255px"><COL style="WIDTH: 223px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt">Mohammed Imthiyaz</TD><TD style="FONT-SIZE: 10pt">Mohammed Imthiyaz PP Services</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt">L-91, Housing Unit</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt">Erode</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt">Chennai</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt">Tamil Nadu</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt">PH:42596628</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt">FAX:638952</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt">PP Services</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt">*****************************************</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt">Joseph Charles</TD><TD style="FONT-SIZE: 10pt">Joseph Charles ABT Services</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt">L-91, Housing Unit</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt">Erode</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt">Chennai</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 10pt">Tamil Nadu</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 10pt">PH:42596628</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 10pt">FAX:638952</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 10pt">ABT Services</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 10pt">*****************************************</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 10pt">Jack Pinto</TD><TD style="FONT-SIZE: 10pt">Jack Pinto JAK Services</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="FONT-SIZE: 10pt">L-91, Housing Unit</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="FONT-SIZE: 10pt">Erode</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="FONT-SIZE: 10pt">Chennai</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="FONT-SIZE: 10pt">Tamil Nadu</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="FONT-SIZE: 10pt">PH:42596628</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="FONT-SIZE: 10pt">FAX:638952</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="FONT-SIZE: 10pt">JAK Services</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="FONT-SIZE: 10pt">*****************************************</TD><TD style="FONT-SIZE: 10pt"></TD></TR></TBODY></TABLE>
As a matter of fact this data is not any client data, i have manipulated and posted for my better understanding. Hope you are clear with my explanation.

Thanks a lot

Imthiyaz

 

[Peter_SSs]

Hi Peter

As per your instruction i have executed the code you have sent on post 9# and this is the result that i got in merging of entire cells from A1 to A8 in column B, but my idea is to get the data merged only with A1 and A8 in column B (i.e Mohammed Imthiyaz PP Services) which you have done screen shot in previous post.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 215px"><COL style="WIDTH: 271px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Mohammed Imthiyaz</TD><TD rowSpan=9>Mohammed Imthiyaz
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
PP Services
*****************************************
</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>L-91, Housing Unit</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Erode</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Chennai</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Tamil Nadu</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>PH:42596628</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>FAX:638952</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>PP Services</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>*****************************************</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Joseph Charles</TD><TD rowSpan=9>Joseph Charles
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
ABT Services
*****************************************
</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>L-91, Housing Unit</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Erode</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Chennai</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>Tamil Nadu</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>PH:42596628</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>FAX:638952</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>ABT Services</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>*****************************************</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Jack Pinto</TD><TD rowSpan=9>Jack Pinto
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
JAK Services
*****************************************
</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>L-91, Housing Unit</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>Erode</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>Chennai</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>Tamil Nadu</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>PH:42596628</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD>FAX:638952</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD>JAK Services</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD>*****************************************</TD></TR></TBODY></TABLE>

I'm sorry, my code may still not be producing exactly what you want, but the above screen shot is not the result of my code in post #9.

a) There is nothing in my post #9 code that merges any cells (look for the word "Merge" - you won't find it).

If you have cells that are physically merged in column B after running my code then they must have already been merged before my code was run.

b) My code only combines 2 cell values only, not all of them. This is the bit of code that does that

Cells(r, 2).Value = Cells(r, 1).Value & " " & Cells(r + 7, 1).Value

 

[quaser15]

Hai Peter

I was in out of station, i couldn't get through for these days, i apologize for not sending reply on time.

Thanks a lot the last code which you have sent is working fantastically. It is showcasing my desired result.

Thanks Again

Imthiyaz

 

[Peter_SSs]

Thanks a lot the last code which you have sent is working fantastically. It is showcasing my desired result.

Thanks Again

Imthiyaz

Glad you got what you wanted and thanks for the feedback.

But last code?
Do you mean you really wanted all 9 of the cells values joined in a merged cell using the code from post #11?
Or did you find that the post #9 code ended up doing what you wanted after all?

 

[quaser15]

Hai Peter

I got desired result based on post#9 containing code. Previously i was asking u to merge A1 and A8 in B, it was done perfectly by post#9 code, but later i come to know that i want to merge A1 and A7, then done some changes in post#9 code:

Cells(r, 2).Value = Cells(r, 1).Value & " " & Cells(r + 6, 1).Value
which yielded good result.

Now for another experiment, i want a code, where the data in A7 comes first followed by the data in A1 merges together in B. Post#9 merges the data which contains A1 first followed by A7 in B, now i want vice versa.

Hope you got idea. I will appreciate that

Thanks

Imthiyaz