Merging cells and copy!
 

Hello,

I have a worksheet with three columns of data (about 1200 rows). The sheet
looks as follows:

------A-------------B---

HE612413400v 7010B
HE612417800v 7020C
HE612422100v 8030D
HE612422200v 9040D
HE612422400v 9050E
HE612422900v 6040F
HE612424400v 5050F
HE612424500v 5741D
HE612424900v 5510D
6565D
5587E
5545E

etcetc

I would like to have a macro that does the following: In column C i would
like to have cell A1 & B1 merged, than A1&B2, than A1&B3 etc till the last
cell in column B and than start over again with A2&B1, A2&B2, A2&B3 etcetc
till all cells in column A are merged with all cells in column B. The result
will look like this

HE612413400v 7010B
HE612413400v 7020C
HE612413400v 8030D
HE612413400v 9040D
HE612413400v 9050E
HE612413400v 6040F
etcetc...(till last cell in B than second cell in A)
HE612417800v 7010B
HE612417800v 7020C
HE612417800v 8030D
etcetc... (till last cell in B than third cell in A)

I hope someone can help me... thanks in advance

Merging cells and copy!
 

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub Merge()
Dim MyRangeA As Range, MyRangeB As Range
Dim X As Long
X = 1
lastrowA = Cells(Rows.Count, "A").End(xlUp).Row
lastrowB = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRangeA = Range("A1:A" & lastrowA)
Set MyRangeB = Range("B1:B" & lastrowB)
For Each A In MyRangeA
For Each B In MyRangeB
Cells(X, 3).Value = A & B
X = X + 1
Next
Next
End Sub


Mike

"TooN" wrote:

Hello,

I have a worksheet with three columns of data (about 1200 rows). The sheet
looks as follows:

------A-------------B---

HE612413400v 7010B
HE612417800v 7020C
HE612422100v 8030D
HE612422200v 9040D
HE612422400v 9050E
HE612422900v 6040F
HE612424400v 5050F
HE612424500v 5741D
HE612424900v 5510D
6565D
5587E
5545E

etcetc

I would like to have a macro that does the following: In column C i would
like to have cell A1 & B1 merged, than A1&B2, than A1&B3 etc till the last
cell in column B and than start over again with A2&B1, A2&B2, A2&B3 etcetc
till all cells in column A are merged with all cells in column B. The result
will look like this

HE612413400v 7010B
HE612413400v 7020C
HE612413400v 8030D
HE612413400v 9040D
HE612413400v 9050E
HE612413400v 6040F
etcetc...(till last cell in B than second cell in A)
HE612417800v 7010B
HE612417800v 7020C
HE612417800v 8030D
etcetc... (till last cell in B than third cell in A)

I hope someone can help me... thanks in advance

Merging cells and copy!
 

Hello Mike,

Thanks for the quick response. The macro works almost perfect there is only
one detail i didnt mention. All the rows start in row 4. I adjusted the macro
but the output starts in row 1. Can you make a small adjustment that the
outpu also starts in row 4. Here is the changed macro:

Sub Merge()
Dim MyRangeA As Range, MyRangeB As Range
Dim X As Long
X = 1
lastrowB = Cells(Rows.Count, "B").End(xlUp).Row
lastrowD = Cells(Rows.Count, "D").End(xlUp).Row
Set MyRangeB = Range("B4:B" & lastrowB)
Set MyRangeD = Range("D4:D" & lastrowD)
For Each B In MyRangeB
For Each D In MyRangeD
Cells(X, 7).Value = B & D
X = X + 1
Next
Next
End Sub

Thanks in advance!

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub Merge()
Dim MyRangeA As Range, MyRangeB As Range
Dim X As Long
X = 1
lastrowA = Cells(Rows.Count, "A").End(xlUp).Row
lastrowB = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRangeA = Range("A1:A" & lastrowA)
Set MyRangeB = Range("B1:B" & lastrowB)
For Each A In MyRangeA
For Each B In MyRangeB
Cells(X, 3).Value = A & B
X = X + 1
Next
Next
End Sub


Mike

"TooN" wrote:

Hello,

I have a worksheet with three columns of data (about 1200 rows). The sheet
looks as follows:

------A-------------B---

HE612413400v 7010B
HE612417800v 7020C
HE612422100v 8030D
HE612422200v 9040D
HE612422400v 9050E
HE612422900v 6040F
HE612424400v 5050F
HE612424500v 5741D
HE612424900v 5510D
6565D
5587E
5545E

etcetc

I would like to have a macro that does the following: In column C i would
like to have cell A1 & B1 merged, than A1&B2, than A1&B3 etc till the last
cell in column B and than start over again with A2&B1, A2&B2, A2&B3 etcetc
till all cells in column A are merged with all cells in column B. The result
will look like this

HE612413400v 7010B
HE612413400v 7020C
HE612413400v 8030D
HE612413400v 9040D
HE612413400v 9050E
HE612413400v 6040F
etcetc...(till last cell in B than second cell in A)
HE612417800v 7010B
HE612417800v 7020C
HE612417800v 8030D
etcetc... (till last cell in B than third cell in A)

I hope someone can help me... thanks in advance

Merging cells and copy!
 

I would avoid Merged Cells they usually cause problems eventually


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site
' (http://www.excel-it.com)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28988

Merging cells and copy!
 

solved it... thanks (x=3)

Thanks!

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub Merge()
Dim MyRangeA As Range, MyRangeB As Range
Dim X As Long
X = 1
lastrowA = Cells(Rows.Count, "A").End(xlUp).Row
lastrowB = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRangeA = Range("A1:A" & lastrowA)
Set MyRangeB = Range("B1:B" & lastrowB)
For Each A In MyRangeA
For Each B In MyRangeB
Cells(X, 3).Value = A & B
X = X + 1
Next
Next
End Sub


Mike

"TooN" wrote:

Hello,

I have a worksheet with three columns of data (about 1200 rows). The sheet
looks as follows:

------A-------------B---

HE612413400v 7010B
HE612417800v 7020C
HE612422100v 8030D
HE612422200v 9040D
HE612422400v 9050E
HE612422900v 6040F
HE612424400v 5050F
HE612424500v 5741D
HE612424900v 5510D
6565D
5587E
5545E

etcetc

I would like to have a macro that does the following: In column C i would
like to have cell A1 & B1 merged, than A1&B2, than A1&B3 etc till the last
cell in column B and than start over again with A2&B1, A2&B2, A2&B3 etcetc
till all cells in column A are merged with all cells in column B. The result
will look like this

HE612413400v 7010B
HE612413400v 7020C
HE612413400v 8030D
HE612413400v 9040D
HE612413400v 9050E
HE612413400v 6040F
etcetc...(till last cell in B than second cell in A)
HE612417800v 7010B
HE612417800v 7020C
HE612417800v 8030D
etcetc... (till last cell in B than third cell in A)

I hope someone can help me... thanks in advance