How to make paste special faster when we have records more than 60,000

[ThrottleWorks]

Hi,

I have a formula in cell A2.
I am pasting this formula in cell A2:A65000.

When I do it manually, excel takes 2-3 minutes to get refreshed and reflect values correctly.
When I do it with VBA, macro does not calculates entire column before triggering next line of code.

I have written activesheet.calculate 3-4 times in the code but still it does not help.
How do I overcome this problem.
I also tried to write a loop for this, but loop is taking too much time to complete.
Or is there any where I can store all the values from FOR EACH LOOP in an array and pass it with a single line once loop is complete

Can anyone please help me in this.

For Each TRng In TempRng
        MySht.Cells(TRng.Row, 1).FormulaR1C1 = "=IF(AND(COUNTIFS(C[1],RC[1],C[9],""Y*""
>0,COUNTIFS(C[1],RC[1],C[9],""N*"")>0),""Yoyo"",""NoNo"")"
        MySht.Cells(TRng.Row, 1).Value = MySht.Cells(TRng.Row, 1).Value
    Next TRng

 

[Hui]

Have you tried setting Calculation to Manual before and setting Calculation to Automatic after your code?

 

[ThrottleWorks]

Hi @Hui sir, no. I have not. I will do and try again.
Thanks a lot for the help. Have a nice day ahead.

 

[chirayu]

I'd say have the macro put in the formula and calculate first and then do the below for value paste

Sub RNGVAL()

Application.Calculation = xlCalculationAutomatic

Dim Rng As Range
Dim LstRow As String

LstRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A2:A" & LstRow)

Rng = Rng.Value

End Sub

 

[marina11]

Thanks for sharing

 

[ThrottleWorks]

Hi I am trying below solutions provided by @shrivallabha sir and @Chihiro sir.

https://chandoo.org/forum/threads/alternate-for-vlookup.30722/

Will revert with details. Have a nice day ahead.

 

[ThrottleWorks]

Hi @shrivallabha sir, thanks a lot.
Your post dated 10-08-2016 helped me resolve this.

Have a nice day ahead.

 

[shrivallabha]

Hi @shrivallabha sir, thanks a lot.
Your post dated 10-08-2016 helped me resolve this.

Have a nice day ahead.

Happy to know this!