Very Very slow VB code..?????

[sezuh]

Hi,
Thanks to wonderful,wonderful help from “GlennUK “ solved one part of this tread;
http://www.mrexcel.com/forum/showthread.php?t=535167
but i’m having trouble with one of the Vb Code it’s very very slow,i started on Friday 21.00 pm
by Monday 06.00 it only finished 5883 rows out 20825 only is that normal??? Even after “Glenn” modified the excel formula still very very slow. Would someone please look at the code and give me some idea to sort the code??
I’m using excel 2007 would that be the problem?thanks in advance for any help.
example51_3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt">INPUT</TD><TD>Output</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1 2 3</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">17296</TD><TD style="TEXT-ALIGN: right">6200</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1 2 3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1 2 4</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">1 2 4</TD><TD> </TD><TD> </TD><TD>Test cells51</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">1 2 5</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1 2 5</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">45</TD><TD style="TEXT-ALIGN: right">49</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">1 2 6</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1 2 6</TD><TD> </TD><TD> </TD><TD>Output51</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">1 2 7</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1 2 7</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">8 19 50</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">1 2 8</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L1</TD><TD>=NOT(ISNA(MATCH(H1,$D$4:$F$4,0)))</TD></TR><TR><TD>M1</TD><TD>=NOT(ISNA(MATCH(I1,$D$4:$F$4,0)))</TD></TR><TR><TD>N1</TD><TD>=NOT(ISNA(MATCH(J1,$D$4:$F$4,0)))</TD></TR><TR><TD>O1</TD><TD>=OR(L1:N1)</TD></TR><TR><TD>P1</TD><TD>=COUNTIF($O$1:O1,FALSE)</TD></TR><TR><TD>Q1</TD><TD>=MAX(P1:P20825)</TD></TR><TR><TD>R1</TD><TD>=RANDBETWEEN(1,Q1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Sub generatecells_51()
    Application.Calculation = xlCalculationManual
    For Each c In Range("A2:A20826")
        myarray = Split(c.Value, " ")
        For i = LBound(myarray) To UBound(myarray)
        [testcells51].Cells(i + 1).Value = Val(myarray(i)): Next
        ActiveSheet.Calculate
        c.Offset(0, 1) = [output51]
    Next
    Application.Calculation = xlCalculationAutomatic
End Sub

Regards
sezuh

 

[mikecroom]

My suggestions:

1 Avoid Randbetween. I am not an expert but it is a so-called volatile function.

"If you’ve used the RAND() or the RANDBETWEEN() functions, you would know this. Anytime even a single cell value is changed in an excel spreadsheet, all cells within the spreadsheet which contain these formulas recalculate themselves, even when the cell being changed is not referenced by any of them at all. Conditional formatting also has the same characteristic. Throw in OFFSET, ROWS, COLUMNS, INDIRECT into the set and now you know why your favorite excel spreadsheet with thousands to these functions has suddenly become so slow! These functions have to be processed at every re-calculation, becoming a potential resource hog when used generously across thousands of cells."

2 Set Application.ScreenUpdating=FALSE whlst the long macro runs. Then reset to TRUE after. But (1) is the big issue I think

 

[mikerickson]

In addition to Application.ScreenUpdating, it would run faster if you set

Application.Calculation = xlManual
    Rem your code
Application.Calculation = xlAutomatic

 

[sezuh]

Hi mike thanks for your response,
but i have another VB Code and it's very very fast and they are nearly the same;
And how to Set Application.ScreenUpdating=FALSE ??
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt">INPUT</TD><TD>Actual Output</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD>1 2 3 4</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">495</TD><TD style="TEXT-ALIGN: right">218</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>1 2 3 4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD><TD>1 2 3 5</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>1 2 3 5</TD><TD></TD><TD>Test cells</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">6</TD><TD>1 2 3 6</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>1 2 3 6</TD><TD></TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">7</TD><TD>1 2 3 7</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>1 2 3 7</TD><TD></TD><TD>Output Cell</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">8</TD><TD>1 2 3 8</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>1 2 3 8</TD><TD></TD><TD>2 4 7 12</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">9</TD><TD>1 2 3 9</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L1</TD><TD>=NOT(ISNA(MATCH(G1,$C$4:$F$4,0)))</TD></TR><TR><TD>M1</TD><TD>=NOT(ISNA(MATCH(H1,$C$4:$F$4,0)))</TD></TR><TR><TD>N1</TD><TD>=NOT(ISNA(MATCH(I1,$C$4:$F$4,0)))</TD></TR><TR><TD>O1</TD><TD>=NOT(ISNA(MATCH(J1,$C$4:$F$4,0)))</TD></TR><TR><TD>P1</TD><TD>=OR(L1:O1)</TD></TR><TR><TD>Q1</TD><TD>=COUNTIF($P$1:P1,FALSE)</TD></TR><TR><TD>R1</TD><TD>=MAX(Q1:Q1820)</TD></TR><TR><TD>S1</TD><TD>=RANDBETWEEN(1,R1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Sub generatecells_16()
    Application.Calculation = xlCalculationManual
    For Each c In Range("A2:A1821")
        myarray = Split(c.Value, " ")
        For i = LBound(myarray) To UBound(myarray)
        [test_cells].Cells(i + 1).Value = Val(myarray(i)): Next
        ActiveSheet.Calculate
        c.Offset(0, 1) = [output_cell]
    Next
    Application.Calculation = xlCalculationAutomatic
End Sub

thanks
sezuh

 

[MrKowz]

What purpose does the ActiveSheet.Calculate line have in here? You are basically calculating the sheet 1820 times, which can make ANYTHING take a while to run. Do you truly have that much data and need to calculate at each loop?

Sub generatecells_16()
    Application.Calculation = xlCalculationManual
    For Each c In Range("A2:A1821")
        myarray = Split(c.Value, " ")
        For i = LBound(myarray) To UBound(myarray)
        [test_cells].Cells(i + 1).Value = Val(myarray(i)): Next
        [COLOR=red][B]ActiveSheet.Calculate[/B][/COLOR]
        c.Offset(0, 1) = [output_cell]
    Next
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[sezuh]

thanks Mike and Mrkows
for your advice,but if you notice both code are the same except range different and i dont have any problem with code in post#4 this one is very fast but code in post #1 is the slow one ,and Mike, it starts with

Application.Calculation = xlCalculationManual

and finish

Application.Calculation = xlCalculationAutomatic

thanks again
sezuh

 

[MrKowz]

Again, what is the purpose of using ActiveSheet.Calculate in your code? Depending on how much data is on the sheet, how many formulas, etc, 20,000 activesheet.calculate is likely what is causing it to be so slow. If you need to recalculate to grab a single value, that would likely be best to do internally to the macro, so you don't have to recalculate what could potentially be hundreds of thousands of formulas over 20,000 times.

 

[sezuh]

Mrkowz, to be honest i have no idea why is "activesheet.calculate " calculating,what i do i implement what you good people provide me
to my excel sheet,i dont know how to write a code or formula.
the intention is in column "A"the list of Combin(51,3)=20825 rows and in column"B" to have the same list but with a differentnumber from the previous column.and all list must be used result 20825=20825 rows in each column. example;
example51_3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt">INPUT</TD><TD>Output</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1 2 3</TD><TD style="TEXT-ALIGN: right">9 11 42</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">1 2 4</TD><TD style="TEXT-ALIGN: right">12 25 43</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1 2 5</TD><TD style="TEXT-ALIGN: right">7 31 50</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1 2 6</TD><TD style="TEXT-ALIGN: right">10 18 41 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1 2 7</TD><TD style="TEXT-ALIGN: right">4 17 22 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">1 2 8</TD><TD style="TEXT-ALIGN: right">5 20 39</TD></TR></TBODY></TABLE>
regards

 

[MrKowz]

Ahh - I think I understand it now. When dealing with randomizing data, and with 20,000+ datapoints, there eventually becomes such a small probability of finding a value that hasn't already been repeated that it could take hours to find a single match.

I see now why the Application.Calculate was required - it is using worksheet functions to find a new data point. However, it is the sheer volume of calculations that is making this slow. From what I can tell, there are eight formulas per row. That makes for 166,608 formulas in your spreadsheet... and calculating that 20,825 times means that Excel is needing to do 3,469,611,600 worksheet calculations.

I'm not the best at randomizing data effeciently, but I think I might have an idea on how to accomplish this. Let me play with a few ideas and see what I can come up with.

However, please clarify if this is your goal - You want to randomly place each value from column A into column B, not repeating, and not on the same rows. (i.e. you won't have 10 35 20 next to 10 35 20)

 

[sezuh]

yes thats what i want...but may i ask if possible to allocate numbers evenly ie; there are 49 rows start with num.1 2 so if we can alocate rows
with remaining numbers equally.and so on..
thanks for all your help and effort
sezuh