I'm trying to pull a huge column of data into an array (500,000 rows), then remove some items that are direct duplicates of previous item, and then basically output it into a new column....
I have been going around in circles for hours now trying to do this and am not getting anywhere..
I figured making two arrays and then trying to transpose would be quickest method...Problem is that there is an application transpose limit for a dataset this large, so that doesnt work..
So I then have downloaded a transpose function to do it manually in VBA and that doesnt work either...
It outputs a column of "#N/A"'s...
Can anyone help me out here?
Thanks,
Ben
I have been going around in circles for hours now trying to do this and am not getting anywhere..
I figured making two arrays and then trying to transpose would be quickest method...Problem is that there is an application transpose limit for a dataset this large, so that doesnt work..
So I then have downloaded a transpose function to do it manually in VBA and that doesnt work either...
Code:
'uniquesDim arr() As Variant
Dim arr2() As Variant
Count = 1
arr = Range("B2:B" & lr)
ReDim arr2(1 To UBound(arr, 1), 0 To 1)
ws.Cells(2, 3).Value = ws.Cells(2, 2).Value
For i = LBound(arr, 1) + 1 To UBound(arr, 1)
If arr(i, 1) = arr(i - 1, 1) Then
'do nothing
Else
arr2(Count, 0) = arr(i, 1)
Count = Count + 1
End If
Next
ws.Range("C3:C" & Count + 2).Value = TransP(arr2)
Code:
Public Function TransP(var As Variant) As Variant Dim outP() As Variant, i As Long, j As Long
ReDim outP(LBound(var, 2) To UBound(var, 2), LBound(var, 1) To UBound(var, 1))
For i = LBound(outP) To UBound(outP)
For j = LBound(var) To UBound(var)
outP(i, j) = var(j, i)
Next
Next
TransP = outP
End Function
It outputs a column of "#N/A"'s...
Can anyone help me out here?
Thanks,
Ben