Why dictionary very slow ,is dictionary have maksimum data

I am using excel 2016 64 bitt
I have data in col a:j
My key is a&b&c...&j
But it very slow,is so multi Compleks criteria
My data 80.000 row

sub test()
Dim txt$,i&,x
With [A1].CurrentRegion
  x = .Value
  With createObject("Scripting.dictionary")
   For i = 1 to Ubound(x)
    With application
     txt = Join(.transpose(.transpose(.index(x,i)),",")
   .item(txt) = .index(x,i)
   End with
 Next i
End with
End with
End sub

But if using array is very fast
But my data not similar arrange data
Must to sort but so complek data imposibble to sort

sub arr()
Dim ar(),i&,n&, x,j&,k&
x =[a1].CurrentRegion.value
  Redim ar(1 to ubound(x),1 to ubound(x,2))
  For i = 2 to ubound(x)
     n = i-1
     For j = 1 to ubound(x,2)
        If x(i,j) = x(n,j) then
          k = k + 1
          ar(1,j) = x(n,j)
          ar(k,j) = x(i,j)
        End if
     Next j
  Next i
End sub

Originally Posted by Kyle123

Don't use worksheet functions on arrays, you may find removing them resolves your issue

What mean
Worksheetfunction
Your mean don't using transpose

Yes, I mean don't use transpose, it's a worksheet function.

Also, your code won't work, you aren't accessing your dictionary, your with blocks are wrong

Originally Posted by Kyle123

Yes, I mean don't use transpose, it's a worksheet function.

Also, your code won't work, you aren't accessing your dictionary, your with blocks are wrong

Its only simulation
But ok will be test and feed back again

still very long time to run

Sub tes()
Dim i&, ii&, x, txt$, d As Object
Set d = CreateObject("scripting.dictionary")
x = [a1:ac100000].Value
 
 For i = 1 To UBound(x)
    For ii = 1 To UBound(x, 2)
      txt = IIf(txt = "", x(i, ii), txt & "," & x(i, ii))
    Next ii
   d(txt) = x(i, 1)
 Next i
    txt = ""
End Sub

What is the code supposed to do?

Originally Posted by shg

What is the code supposed to do?

Only this code i only see in lokal window but so long to run

Is "txt" supposed to start blank after each row? Because if it is, then txt = "" is in the wrong place.... by the end of your loop, txt would contain all columns and rows?

For i = 1 To UBound(x)
    For ii = 1 To UBound(x, 2)
      txt = IIf(txt = "", x(i, ii), txt & "," & x(i, ii))
    Next ii
   d(txt) = x(i, 1)
 Next i
    txt = "" 'should this be BEFORE "Next i" ?
End Sub

Not sure how much that would affect the speed, but it just stood out to me

@SHG I gather he is in early stages of a macro, filling in a dictionary with concatenated values for all columns in A:AC?

Originally Posted by Arkadi

Is "txt" supposed to start blank after each row? Because if it is, then txt = "" is in the wrong place.... by the end of your loop, txt would contain all columns and rows?

For i = 1 To UBound(x)
    For ii = 1 To UBound(x, 2)
      txt = IIf(txt = "", x(i, ii), txt & "," & x(i, ii))
    Next ii
   d(txt) = x(i, 1)
 Next i
    txt = "" 'should this be BEFORE "Next i" ?
End Sub

Not sure how much that would affect the speed, but it just stood out to me

@SHG I gather he is in early stages of a macro, filling in a dictionary with concatenated values for all columns in A:AC?

Arkadi i am has input to after for i, is same as so very long to run