working on range

[xls]

Hi,

I have 2 columns in which data is as follows:

Col-A Col-B
A 1
B 2
3

I wants this data to be pulated in two other column in same sheet in two diff. coloumn as follows

COL D COL-F

A 1
A 2
A 3
B 1
B 2
B 3

In short I have to repeat all cells in col A for Range Data given for Col B.

Pl help me to how top do it. Because data is running in 100 of cell where it is not possible to do manually always.
:dunno

[GTO]

Greetings xls,

If I am understanding correctly, in a throwaway copy of your wb...

In a Standard Module:

Option Explicit

Sub EXA()
Dim _
lLRowA As Long, _
lLRowB As Long, _
aryBase As Variant, _
arySub As Variant, _
aryCompiled As Variant, _
x As Long, _
y As Long, _
xx As Long

With ThisWorkbook.Worksheets("Sheet1")
'// Find the row number of the loweermost cell containing data //
lLRowA = .Cells(Rows.Count, 1).End(xlUp).Row
lLRowB = .Cells(Rows.Count, 2).End(xlUp).Row

'// Assign the values of the two ranges to two arrays. //
aryBase = .Range("A2:A" & lLRowA).Value
arySub = .Range("B2:B" & lLRowB).Value

'// Size another array to hold our compiled values. //
ReDim aryCompiled(1 To (UBound(aryBase, 1) * UBound(arySub, 1)), 1 To 2)

'// loop thru until we fill our big array, ... //
xx = 1
For x = 1 To UBound(aryBase, 1)
For y = 1 To UBound(arySub, 1)
aryCompiled(xx, 1) = aryBase(x, 1)
aryCompiled(xx, 2) = arySub(y, 1)
xx = xx + 1
Next
Next
'// ...then dump the big array onto the sheet. //
.Range("D2").Resize(UBound(aryCompiled, 1), 2).Value = aryCompiled
End With
End Sub


Hope that helps,

Mark

[Bob Phillips]

Is it the last cell in column A that determines how many repeats?

[xls]

It is not always. I may select both range with Mouse

[GTO]

I take it I misunderstood?

[Bob Phillips]

It is not always. I may select both range with Mouse

So how will the code know?

[GTO]

xls,

Just wondering, did you get this resolved?

Mark