Copy and paste as values

Question

I have the code that in general works but it takes forever to run it - I always need to to break it by pressing 'Esc' becuase otherwise I could wait all day long. When I do press 'Esc' the code in general exceutes what it should. But it is annoying and I would like to have it work smoothly.

My code is supposed to execute simple Index formula in one column until then end of the table (i.e.match some word in the other sheet based on the column in front and return it as the result), then it should copy and paste the content in that column to make the formula disappear and leave only the returned values.

Option Explicit
Sub Match_CopyPaste()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim r As Long
Dim endRow As Long
Dim TargetRow As Long
Const ColumnStart As Integer = 2
Const ColumnEnd As Integer = 2

TargetRow = 4


With ThisWorkbook.Sheets("Sheet1")

                 '*********Clear what is inside********'

    .Range(.Cells(TargetRow, ColumnStart), .Cells(.Rows.Count, ColumnEnd)).ClearContents

    .Range("A4", .Cells(Rows.Count, "A").End(xlUp)).Offset(0, 1).FormulaR1C1 = "=IFERROR(INDEX(Array,MATCH(RC[-1],Name,0),2),"""")"

End With


'***Part where the problem is:*******
    With ThisWorkbook.Sheets("Sheet1")
           '************** Copy and paste it as values*********
     endRow = .Cells(.Rows.Count, ColumnEnd).End(xlUp).Row     
        For r = 4 To endRow

        Cells(r, ColumnEnd).Value = Cells(r, ColumnEnd).Value
        Next r

    End With



    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    End Sub

I will add that it is the second part of the code (Copy and paste it as values) where the code breaks. Is it something in the code, like the order or structure that makes it impossible to fill long column down ?

A loop is inefficient - grab the entire range from row 4 to endRow and hard code the values in one go. — BigBen, Aug 14 '18 at 13:12
You may want to put a breakpoint and inspect the value of `endRow`. While a loop is not most efficient for this operation, it's not "normal" that you have to break out of your code with the Cancel key. It's possible `endRow` is significantly larger value than you expect it to be, in which case you may want to revisit [how you find the "last" row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). — David Zemens, Aug 14 '18 at 13:28

score 0 · Accepted Answer · answered Aug 14 '18 at 13:19

0

as per BigBen comment:

With ThisWorkbook.Sheets("Sheet1")
       '************** Copy and paste it as values*********
    With .Range(.Cells(4, ColumnEnd), .Cells(.Rows.Count, ColumnEnd).End(xlUp))
        .Value = .Value
    End With
End With

answered Aug 14 '18 at 13:19

DisplayName

13,283
2
11
19

Copy and paste as values

1 Answers1