Optimize macro

Nagini

New Member
Joined
Dec 11, 2016
Messages
34
Hello! I've written this macro, it takes 5 minutes to end the work and I think that maybe someone Knows how to optimize it to make ir faster!


Sub Macro2()


Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


Dim Indices, Calculos, Seleccion As Worksheet
Set Indices = Sheets("C.Indices")
Set Calculos = Sheets("CALCULOS")
Set Seleccion = Sheets("Sel.Mes")


Dim i As Integer
LastCol = Indices.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 8 To LastCol

Dim j As Integer

j = i - 6

Indices.Columns(i).Copy

Calculos.Select
Columns("D:D").PasteSpecial Paste:=xlPasteValues
Application.Goto Reference:="R318C50"
Range("BR318:CK318").Copy
Seleccion.Select
Cells(j, 1).PasteSpecial Paste:=xlPasteValues

Next i


Application.ScreenUpdating = True

Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
Application.CutCopyMode = False


End Sub
 
I'm using:

Windows 10 Pro Version 1607 running with parallels on a Macbook pro

Nagini,

I have no experience with a Mac.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
That said how much memory is allocated to your windows virtual machine under parallels
 
Upvote 0
I have 2Gb out of 8! I didn't know that I could change that, thank you very much. Gonna try with 4Gb lets see what happens
 
Upvote 0
Don't push it too high as mac will still need some to run itself, google parallels and memory allocation it should give you some guidelines maybe it is running at some optimum default and it's just the way it runs as opposed to native Windows
 
Upvote 0
Does your spreadsheet contain data or complex formulas
 
Upvote 0
Well each column that the macro copies has about 300rows of data, and there are some formulas like this =(E59)+(((D60-E59)*2)/(1+1)), I mean, they aren't very complex, I think that even though it can go faster (at least as fast as at the computer I have at work)
 
Upvote 0
One ENORMOUS way to save time in an Excel macro is to NOT use copy/paste if at all possible! And since you are using PasteValues, you don't need to use copy/paste. For example, if we take the code Hiker created in post #7 and make the following changes:

Rich (BB code):
Sub Macro2_V2()
Dim Indices As Worksheet, Calculos As Worksheet, Seleccion As Worksheet
Dim i As Long, j As Long, LastCol As Long, t As Double, CalcRange As Variant

    t = Timer
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

    Set Indices = Sheets("C.Indices")
    Set Calculos = Sheets("CALCULOS")
    Set Seleccion = Sheets("Sel.Mes")

    LastCol = Indices.Cells(1, Columns.Count).End(xlToLeft).Column
    CalcRange = Calculos.Range("BR318:CK318").Value

    For i = 8 To LastCol

        j = i - 6

        Indices.Columns(i).Copy
        Calculos.Range("D1").PasteSpecial Paste:=xlPasteValues
        Seleccion.Range("A" & j & ":T" & j).Value = CalcRange
'        Calculos.Range("BR318:CK318").Copy
'        Seleccion.Cells(j, 1).PasteSpecial Paste:=xlPasteValues

        Application.CutCopyMode = False

    Next i
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Debug.Print Timer - t
End Sub

on my test sheet the time went down from 14 seconds to 7. As far as I can tell, the results are the same, although you'd need to test it. That one change has 2 major effects, first, it reads the static range once only, not every time through the loop, and second, it just uses = to get the values, not .Copy.

And I really believe that you can change this:

Rich (BB code):
Indices.Columns(i).Copy
Calculos.Range("D1").PasteSpecial Paste:=xlPasteValues
to something like:
Rich (BB code):
    LR = last row of columns(i)
    Calculos.Range("D1:D" & lr).Value = Indices.Range(Cells(1, i), Cells(LR, i)).Value
and cut down the remaining time by another 75%.

And this may be a bug, but every column from Indices is being pasted to column D of Calculos. If that's really the case, then you're just pasting data on top of data on top of data, and not accomplishing anything. If that's what you really want, take that out of the loop, and you only need to execute it once for the final column.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top