5
\$\begingroup\$

Novice coder here. I have the following code to perform a few actions like clearing filters, copying a range, and then pasting a formula down a row till the last line.

It works perfectly, but it takes longer than a minute to complete since I am addressing each in my for next loop individually. Would anyone know how to speed this up by using a range call instead of a cell call?

My constraint is that I must start on row six of the column selected and autofill to the bottom. The same with the paste values of the column to the left.

 Sub testingme2()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim myvalue As Double, lastrow As Long, paster As String

lastrow = Worksheets("x").Cells(Rows.Count, 1).End(xlUp).Row
myvalue = Worksheets("y").Cells(6, 2).Value
paster = "formula"

If Sheets("x").FilterMode Then
    Sheets("x").Cells.AutoFilter
End If

Sheets("x").Select
Range("A6:AG5000").Select
Selection.Copy
Sheets("z").Select
Range("A6").Select
Selection.PasteSpecial xlPasteValues
Sheets("x").Select

For i = 6 To lastrow
    Worksheets("x").Cells(i, (myvalue) + 20).Copy
    Worksheets("x").Cells(i, (myvalue) + 20).PasteSpecial xlPasteValues
    Worksheets("x").Cells(i, (myvalue) + 21).Cells.FormulaR1C1 = paster
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
\$\endgroup\$
6
\$\begingroup\$

This should be much faster:

  1. We get rid of the .Select and .Activate. They will slow the process down because it is telling excel to do something, that takes time.

  2. Since we only want the values we assign them directly avoiding the clipboard. Again just the moving of data into the clipboard takes an extra step and then moving them out again is another step. We can do it in one.

  3. When using R1C1 we can put the formula in bulk, so no looping. The R1C1 references will adjust accordingly.

  4. Using the With block and proper . notation allows us to limit typing the sheet over and over again, but still allowing the proper parentage to be assigned.


Sub testingme2()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim myvalue As Double, lastrow As Long, paster As String

lastrow = Worksheets("x").Cells(Rows.Count, 1).End(xlUp).Row
myvalue = Worksheets("y").Cells(6, 2).Value
paster = "formula"

If Sheets("x").FilterMode Then
    Sheets("x").Cells.AutoFilter
End If


Sheets("Z").Range("A6:AG5000").Value = Sheets("x").Range("A6:AG5000").Value



With Worksheets("x")
    .Range(.Cells(6, (myvalue) + 20), .Cells(lastrow, (myvalue) + 20)).Value = .Range(.Cells(6, (myvalue) + 20), .Cells(lastrow, (myvalue) + 20)).Value
    .Range(.Cells(6, (myvalue) + 21), .Cells(lastrow, (myvalue) + 21)).FormulaR1C1 = paster
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
\$\endgroup\$
  • 2
    \$\begingroup\$ Awwwww yissssss Scott's here. \$\endgroup\$ – findwindow Jun 17 '16 at 16:39
  • \$\begingroup\$ Absolutely amazing! Thanks. My only question is for the 1st range line in the with section: Was the last part supposed to be , .Cells(lastrow, (myvalue) + 2)).Value or , .Cells(lastrow, (myvalue) + 20)).Value. I believe it is 20. Correct? And thank you so much! this code executed in less then 1 second. \$\endgroup\$ – E.Pyles Jun 17 '16 at 17:23
  • \$\begingroup\$ @E.Pyles yes I mistyped. I have fixed it. See edit. Please mark as correct by clicking the check mark by the answer. \$\endgroup\$ – Scott Craner Jun 17 '16 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.