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

    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")
        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:



    Paste Value


    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