Fast method to transfer values without using clipboard?

[Dobo_Bobo]

I am teaching myself VBA and I have been running a certain task via Copy and PasteSpecial Paste:=xlPasteValues and it completes the macro within 7 seconds. The task is to move the values to the left by one column.

I've recently read the clipboard should be avoided when possible thus I used an alternative method which was suggested. However, I've found this method results in the macro taking 40 seconds to complete! Thus, please can someone tell me if there is another way that enables my macro to run as fast as the clipboard method, but without using the clipboard?


An extract of the alternative method is below, but please note this is just one section, there are about 200 lines of code required to transfer values.


If anyone can help I will really appreciate it and I will return to give thanks.

Sub Test()

'TRANSFER VALUES
'Team A
Worksheets("Sheet1").Range("U471:AE471").Value = Worksheets("Sheet1").Range("V471:AF471").Value
Worksheets("Sheet1").Range("U473:AE473").Value = Worksheets("Sheet1").Range("V473:AF473").Value
Worksheets("Sheet1").Range("U475:AE475").Value = Worksheets("Sheet1").Range("V475:AF475").Value

'Team B
Worksheets("Sheet1").Range("U477:AE477").Value = Worksheets("Sheet1").Range("V477:AF477").Value
Worksheets("Sheet1").Range("U479:AE479").Value = Worksheets("Sheet1").Range("V479:AF479").Value
Worksheets("Sheet1").Range("U481:AE481").Value = Worksheets("Sheet1").Range("V481:AF481").Value


'Team C
Worksheets("Sheet1").Range("U483:AE483").Value = Worksheets("Sheet1").Range("V483:AF483").Value
Worksheets("Sheet1").Range("U485:AE485").Value = Worksheets("Sheet1").Range("V485:AF485").Value
Worksheets("Sheet1").Range("U487:AE487").Value = Worksheets("Sheet1").Range("V487:AF487").Value


'Team D
Worksheets("Sheet1").Range("U489:AE489").Value = Worksheets("Sheet1").Range("V489:AF489").Value
Worksheets("Sheet1").Range("U491:AE491").Value = Worksheets("Sheet1").Range("V491:AF491").Value
Worksheets("Sheet1").Range("U493:AE493").Value = Worksheets("Sheet1").Range("V493:AF493").Value


'Team E
Worksheets("Sheet1").Range("U495:AE495").Value = Worksheets("Sheet1").Range("V495:AF495").Value
Worksheets("Sheet1").Range("U497:AE497").Value = Worksheets("Sheet1").Range("V497:AF497").Value
Worksheets("Sheet1").Range("U499:AE499").Value = Worksheets("Sheet1").Range("V499:AF499").Value

End Sub

 

[6StringJazzer]

I'm not sure why this method would be slower than copy/paste.

Do the even-numbered rows also have data but you don't want to touch it? If you don't have to avoid them, you can include the even rows then this could be done in one line of code, which would be hella faster. (If the even-numbered rows do have data that you don't want to change, you may also want to revisit your design.)

Regardless, add these lines of code at the beginning and end. This should give a noticeable improvement:

Application.ScreenUpdating = False

' your code in here

Application.ScreenUpdating = True

Also, you could also do this copy with a loop:

Dim R As Long ' row number
For R = 471 To 871  Step 2 ' revise to suit
   Worksheets("Sheet1").Range(Cells(R, "U"), Cells(R, "AE")).Value = Worksheets("Sheet1").Range(Cells(R, "V"), Cells(R, "AF")).Value
Next R

This won't run any faster but you would have three lines of code instead of 200.

 

[RSpin]

If the rest of your data follows the same pattern, then this may do it for you:
<code>

Dim AR1() As Variant
Dim BB As Integer
 
'TRANSFER VALUES
'Teams A through E
Application.ScreenUpdating = False
For BB = 471 To 499 Step 2
    AR1 = Range("V" & BB & ":AF" & BB)
    Range("U" & BB & ":AE" & BB) = AR1
Next BB
Application.ScreenUpdating = True

<code>
hope this helps.</code></code>

 

[Dobo_Bobo]

Hi 6StringJazzer and RSpin, thank you very much for taking the time provide alternative code! I tested both and using the clipboard is still far faster but even though it is I am still grateful for your help! I have been learning VBA for the past year and I have never come across the type of code you have provided so thank you for helping me to learn, I'm sure both will come to good use in future.

To provide clarity for those reading this thread. In my original post I forgot to mention that I am also using the code below in an attempt to speed up the code, which is it does for using the clipboard. If anyone knows why the clipboard is faster than the other methods provided in this thread I will be grateful for the knowledge. Thanks.

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

'my macro code for the value transfer goes here

Application.EnableEvents = True
Application. DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[MARK858]

If anyone knows why the clipboard is faster than the other methods provided in this thread I will be grateful for the knowledge

I doubt if anyone can without you showing the exact code that you are using for the copy/pastespecial

 

[Dobo_Bobo]

Hahaha true Mark. When I tried posting the full code I was encountering an issue where the code was displayed beyond the [/CODE] thus I didn't want to mess up the thread. I think I've fixed it now, so the code for my testing would be:

Sub Test()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

'TRANSFER VALUES
'Team A
Worksheets("Sheet1").Range("U471:AE471").Value = Worksheets("Sheet1").Range("V471:AF471").Value
Worksheets("Sheet1").Range("U473:AE473").Value = Worksheets("Sheet1").Range("V473:AF473").Value
Worksheets("Sheet1").Range("U475:AE475").Value = Worksheets("Sheet1").Range("V475:AF475").Value

'Team B
Worksheets("Sheet1").Range("U477:AE477").Value = Worksheets("Sheet1").Range("V477:AF477").Value
Worksheets("Sheet1").Range("U479:AE479").Value = Worksheets("Sheet1").Range("V479:AF479").Value
Worksheets("Sheet1").Range("U481:AE481").Value = Worksheets("Sheet1").Range("V481:AF481").Value


'Team C
Worksheets("Sheet1").Range("U483:AE483").Value = Worksheets("Sheet1").Range("V483:AF483").Value
Worksheets("Sheet1").Range("U485:AE485").Value = Worksheets("Sheet1").Range("V485:AF485").Value
Worksheets("Sheet1").Range("U487:AE487").Value = Worksheets("Sheet1").Range("V487:AF487").Value


'Team D
Worksheets("Sheet1").Range("U489:AE489").Value = Worksheets("Sheet1").Range("V489:AF489").Value
Worksheets("Sheet1").Range("U491:AE491").Value = Worksheets("Sheet1").Range("V491:AF491").Value
Worksheets("Sheet1").Range("U493:AE493").Value = Worksheets("Sheet1").Range("V493:AF493").Value


'Team E
Worksheets("Sheet1").Range("U495:AE495").Value = Worksheets("Sheet1").Range("V495:AF495").Value
Worksheets("Sheet1").Range("U497:AE497").Value = Worksheets("Sheet1").Range("V497:AF497").Value
Worksheets("Sheet1").Range("U499:AE499").Value = Worksheets("Sheet1").Range("V499:AF499").Value

Application.EnableEvents = True
Application. DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[MARK858]

I don't see any PasteSpecial code there

 

[offthelip]

One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So assuming that you don't have formulae in this range you can use a variant array which will only mean accessing the worksheet twice which should make it much faster.
code untested!!

inarr = Worksheets("Sheet1").Range("U471:AF499") 
For i = 1 To 28 Step 2
  For j = 1 To 10
    inarr(i, j) = inarr(i, j + 1)
  Next j
 Next i
Worksheets("Sheet1").Range("U471:AF499")=inarr

 

[Dobo_Bobo]

Many thanks offthelip. I see what you mean, like 6StringJazzer says I need to revisit the design because I have formulas in the even rows. It was all designed long before I knew what VBA was, so once I fix the design I can retest the code.

Thanks for providing your code too, I couldn't get it to work but I haven't learnt functions yet, so I will try again once I have.

 

[RSpin]

If you redesign it with all of the transferable data together, you could do something like this:

Sub tfer_test()


Dim AR1() As Variant


'TRANSFER VALUES
'Teams A through E
Application.ScreenUpdating = False


AR1 = Range("V471:AF499")
Range("U471:AE499") = AR1


Application.ScreenUpdating = True


End Sub

transfering all of the data at the same time (just update the range to reflect the entire range you want to use). I imagine that would speed it up a bit.