none
Paste only value but not formula for Large data set particularly for string in VBA. Any idea please RRS feed

  • General discussion

  • I have tried to solve it different ways but do not get result in 10-20 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).Row 

    Range("N2:N" & lastrow).Copy
    Range("N2").PasteSpecial (xlPasteValuesAndNumberFormats)
    Application.CutCopyMode = False

    Q. It takes 5-10 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
    Next

    Application.CutCopyMode = False


    Friday, June 15, 2018 5:41 AM

All replies

  • Try this instead

    Sub TestMacro()
        Dim x As Workbook
        Set x = Workbooks.Open("C:/Users/Desktop/program/DataManipulation.xlsm")
        Application.CalculateFull
        With x.Sheets(1).Range("N:N")
            .Value = .Value
        End With
    End Sub

    Tuesday, June 19, 2018 8:57 PM
  • Hi Bernie Deitrick,

    Thanks for your response. But unfortunately, it takes long time as like as my other code.

    Wednesday, June 20, 2018 1:12 PM
  • 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

    Wednesday, June 20, 2018 9:11 PM
  • 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).Value

    It also not work for me. 

    Friday, June 22, 2018 8:56 PM