Merging cells and copy!

  • Thread starter Thread starter TooN
  • Start date Start date
T

TooN

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
 
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
 
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!
 
solved it... thanks (x=3)

Thanks!

Mike H said:
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
Click to expand...
 
Back
Top