A Looping Question

Dear all,

How can I do the followings... thanks a lot!

Column A:
A
B
C
D

Column B:
1
2
3
4

I want to combine values of col A & B,
i.e add a new column in Col D, pls see below format:

A1
A2
A3
A4
B1
B2
B3
B4
C1
C2
C3
C4
D1
D2
D3
D4

Hope this is clear.. Pls advise.:)

Hi Snoopies,

There are a number of ways you can do this, depending on how advanced you want to get. Take a look at the following 3 examples:

Code:

---

Sub snoopiesRanges() 'Loop through each cell in ranges then combine
 Dim RNG1 As Range, RNG2 As Range, dRow As Long, CLL1 As Range, CLL2 As Range
Set RNG1 = Range("A1", Range("A65536").End(xlUp)) 'could just use Range("A1:A4")
 Set RNG2 = Range("B1", Range("B65536").End(xlUp)) 'could just use Range("B1:B4")
dRow = 1
 For Each CLL1 In RNG1.Cells
  For Each CLL2 In RNG2.Cells
  Range("D" & dRow) = CLL1.Text & CLL2.Text
  dRow = dRow + 1
  Next 'CLL2
 Next 'CLL1
End Sub


Sub snoopiesArray1() 'transfer cell values to array before combining
 Dim RNG1, RNG2, dRow As Long, CLL1, CLL2
RNG1 = Range("A1", Range("A65536").End(xlUp)).Value 'could just use Range("A1:A4").Value
 RNG2 = Range("B1", Range("B65536").End(xlUp)).Value 'could just use Range("B1:B4").Value
dRow = 1
 For Each CLL1 In RNG1
  For Each CLL2 In RNG2
  Range("D" & dRow) = CLL1 & CLL2
  dRow = dRow + 1
  Next 'CLL2
 Next 'CLL1
End Sub


Sub snoopiesArray2() 'pre-set array values
 Dim RNG1, RNG2, dRow As Long, CLL1, CLL2
RNG1 = Array("A", "B", "C", "D")
 RNG2 = Array(1, 2, 3, 4)
dRow = 1
 For Each CLL1 In RNG1
  For Each CLL2 In RNG2
  Range("D" & dRow) = CLL1 & CLL2
  dRow = dRow + 1
  Next 'CLL2
 Next 'CLL1
End Sub

---

The snoopiesRanges loops through each cell in both ranges, the snoopiesArray1 sets the values of the two ranges to the array variables, and the snoopiesArray2 sets the array values in the code rather than depend on the cell values. Up to you which way you want to use!
Matt

Hi Matt,

Thank you so much for your prompt reply.
It works! I'll know how to do it in the next time :rotlaugh: