0

I am using a VBA containing a lot of chunks similar to the following:

Range("D82").Select
Selection.Copy
Range("L" & i + 4).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I am wondering whether this the fastest way to do such tasks. Is there a way to speed up by using different coding?

Community
  • 1
  • 1
LaTeXFan
  • 1,136
  • 4
  • 14
  • 36
  • 3
    Avoid .Select Copy an entire range in one go, rather than loop, if possible. Difficult to tell with just the code above what else. Switch off things like screenupdating. Can you show more of the code? Also, if the code works but needs speeding up consider posting to [code review](https://codereview.stackexchange.com/) site instead but be sure to read their help pages for posting. – QHarr Apr 01 '18 at 09:41
  • 3
    See [Avoiding select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – QHarr Apr 01 '18 at 09:44
  • @Qharr Why doesn't this work, please? `Range("L" & j, "Q" & j).Value = Range("Mean").Value / Range("Units").Value` The error is "Type Mismatch" – LaTeXFan Apr 01 '18 at 10:00
  • Strange, still not working. It seems that the problem is `/ Range("Units").Value`. This range is one cell containing the unit (eg, 1000, 1000000). – LaTeXFan Apr 01 '18 at 10:07
  • 1
    How have you declared j? And what is in Range("Units") ? – QHarr Apr 01 '18 at 10:08
  • I did not declare j. It is a simple looping variable `For j = Range("Scen_Start").Value To Range("Scen_End").Value`. In Range("Units"), it is just a cell, a constant 1000 or 1000000. – LaTeXFan Apr 01 '18 at 10:10
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167983/discussion-between-latexfan-and-qharr). – LaTeXFan Apr 01 '18 at 10:14

0 Answers0