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
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
10-28-2009, 01:19 AM
Is it the last cell in column A that determines how many repeats?
It is not always. I may select both range with Mouse
I take it I misunderstood?
Bob Phillips
10-28-2009, 06:17 AM
It is not always. I may select both range with Mouse
So how will the code know?
xls,
Just wondering, did you get this resolved?
Mark
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.