Asked by:
Paste only value but not formula for Large data set particularly for string in VBA. Any idea please
General discussion

I have tried to solve it different ways but do not get result in 1020 sec. I have 50000 data in column that comes by using formula. I want to copy it and paste special so that I want to get only values not formula. Ex. 1
Dim x As Workbook
Set x = Workbooks.Open("C:/Users/Desktop/program/DataManipulation.xlsm")
Dim lastrow As Long
lastrow = x.Sheets(1).Range("L100000").End(xlUp).RowRange("N2:N" & lastrow).Copy
Range("N2").PasteSpecial (xlPasteValuesAndNumberFormats)
Application.CutCopyMode = FalseQ. It takes 510 min to get results. What is the problem in here. I am working in MS O 2007.
Ex. 2
For i = 2 To lastrow
Range("N" & i).Copy
Range("N" & i).PasteSpecial xlPasteValues
NextApplication.CutCopyMode = False
 Edited by Rasel Biswas Saturday, June 16, 2018 7:02 PM
All replies



Every time you write a value to a single cell with VBA, Excel will recalculate: your original code was going cell by cell, meaning you had:
PasteValue
Recalculate
Paste Value
ReCalculate
My code converts the whole column in one go, so unless you modified my code, it should be faster than yours.
You can also try this version, which in my experience will result in the speediest performance:
Sub SpeedCode()
Dim x As Workbook
Dim xlCalc As XlCalculation
Set x = Workbooks.Open("C:/Users/Desktop/program/DataManipulation.xlsm")
With Application
.ScreenUpdating = False
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
End With
With x.Sheets(1).Range("N:N")
.Value = .Value
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.Calculation = xlCalc
End With
End Sub 
Hi Bernie,
Thanks for your valuable time and effort. I understand your description very well. Thanks for the explanation. Unfortunately, your one also does not work for me. Previously, I have done in the following way:
Range("M2:M" & lastrow).Select
Range("M2:M" & lastrow).Value = Range("M2:M" & lastrow).ValueIt also not work for me.