0

i have the following code, where i paste a formula to a range of cells. the problem is, that is slows down my excel sheet massively. how can i only add the calculated values instead of the formula itself?

 Set rng = Range("C4") 'select cell where first value of the filtered values is

   Range(rng, rng.End(xlDown)).Offset(, 1).FormulaR1C1 = "=countif(C[-3]:C[-3] ,R[]C[-1])"
    Range(rng, rng.End(xlDown)).Offset(, 1).Copy
    Range(rng, rng.End(xlDown)).Offset(, 1).PasteSpecial xlPasteValues
  • 2
    Have a look at https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba - not going to fix your question but it will improve your code. – aucuparia Feb 21 '20 at 08:59
  • 2
    And the answer to https://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues will fix your question. – aucuparia Feb 21 '20 at 09:00
  • thank you, but regarding your first comment : if i change it to a range, then i dont know how to adapt my 3rd line of code –  Feb 21 '20 at 09:04
  • 2
    Easy in your case: delete the first line and replace `Selection` with `Range("c4")` everywhwere. Or, better, declare a `Range` variable, assign `Range("c4")` to it and use that. – aucuparia Feb 21 '20 at 09:09
  • thx. that worked. do you have a different solution for the pastespecial xlpastevalues thing? is extremely slow for me. i adapted my question –  Feb 21 '20 at 09:20
  • nevermind. find a solution Range(rng, rng.End(xlDown)).Offset(, 1).Value = Range(rng, rng.End(xlDown)).Offset(, 1).Value –  Feb 21 '20 at 09:31

0 Answers0