Create list with arrays

[PcMax]

Hi,

I have a list in column "A" data based on whole number and this number should I add a few lines numbered.
Use this routine and I was wondering if the syntax is correct

In column "A", i for example these values​​:

12
15
7
11
128

I get the list in column "F" has:

Repeats 12 times the value = 1
Repeats 15 times the value = 2
Repeats 07 times the value = 3
Repeats 11 times the value = 4
Repeats 128 times the value = 5


Sub Lista_Con_Array()
Dim r As Long
Dim i As Long
Dim Riga As Variant
Dim Data As Variant
Dim Num As Integer
i = 0
r = Range("A" & Rows.Count).End(xlUp).Row
Data = Range("A1:A" & r) 'Original List
Dim varData As Variant
ReDim varData(1)
For Each Riga In Data
Num = Num + 1
For NumI = 1 To Riga
ReDim Preserve varData(i)
varData(i) = Num
i = i + 1
Next
Next
Range("F1:F" & UBound(varData) + 1) = Application.Transpose(varData)
End Sub

I would like if possible to get a better performance than having to operate with 1000 values ​​other than repeating. I refer to a range extended data Excel 2007.

Thanks in advance

[Admin]

Hi

how about this ?


Sub kTest()

Dim k(), ka, i As Long, j As Long, n As Long

ka = Range("a1:a" & Range("a" & Rows.Count).End(3).Row)
ReDim k(1 To UBound(ka, 1) * Application.Max(ka), 1 To 1)

For i = 1 To UBound(ka, 1)
j = 1
Do While j <= ka(i, 1)
n = n + 1
k(n, 1) = i
j = j + 1
Loop
Next
[f2].Resize(n) = k

End Sub

[PcMax]

Hi,

Thank you for answer.

The code that is definitely improved, I was not continually called Redim Preserve ...

I have to make a comparison using two different array before knowing the value *Application.Max(ka)
I hope I do not see how to work find it difficult, now I try to insert and include this cycle.