+ Reply to Thread
Results 1 to 4 of 4

want to speed up a loop algorithm

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-03-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    120

    want to speed up a loop algorithm

    whenever i run this vba it takes 10-15 minutes. is there a way to do this quicker or do i have to upgrade my hardware? thanks

    Sub Get_Max_And_Min()
    Dim WB As Workbook
    Dim A As Long
    Dim StartFn As Long
    Dim EndFn As Long
    
    StartFn = 30
    EndFn = 130
    
        Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\huraa1.xlsb")
        Application.Workbooks.Open ("C:\Users\wolfgang\Documents\recording.xlsx ")
    
    For A = StartFn To EndFn
    
        Set WB = Application.Workbooks.Open("C:\Users\wolfgang\Documents\M" & A & ".xlsb ")
        Windows("huraa1").Activate
        Range("A2:F94190").ClearContents
        WB.Activate
        Range("A1:F94153").Copy
        Windows("huraa1").Activate
        Range("A2").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Application.Calculate
      WB.Activate
        Range("G1").Copy
        Windows("huraa1").Activate
        Range("BH2").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
      Range("BE2:BH3").Copy
        Windows("recording").Activate
        Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Windows("huraa1").Activate
        Range("A2:F94190").ClearContents
        WB.Activate
        Range("A94152:F174675").Copy
        Windows("huraa1").Activate
       Range("A2").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Application.Calculate
    WB.Activate
        Range("G1").Copy
        Windows("huraa1").Activate
        Range("BH2").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Range("BE2:BH3").Copy
        Windows("recording").Activate
        Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        WB.Close SaveChanges:=False
    Next
    End Sub

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: want to speed up a loop algorithm

    You have a loop 30 to 130 so line that you can delete will save 100 actions.

    You don't say if you have taken the basic steps to speed up excel.

    It is good practice to run something like my Optimise subroutine before running as macro with major loops:-

    
    Sub optimise()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.DisplayStatusBar = False
    'Application.Calculation = xlCalculationManual
    ActiveSheet.DisplayPageBreaks = False
    End Sub
    You need to run Deoptimise to reverse its effects:-

    
    Sub Deoptimise()
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    'Application.Calculation = xlCalculationAutomatic
    ActiveSheet.DisplayPageBreaks = True
    End Sub

    You should be aware as to the affects of the above. So if you want excel to calculate a workbook during your code, don't set the calculation mode to manual.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: want to speed up a loop algorithm

    See if it still works - let me know if it's any quicker

    Sub Get_Max_And_Min()
    Dim wb As Worksheet, wh As Worksheet, wr As Worksheet
    Dim A As Long
    Dim StartFn As Long
    Dim EndFn As Long
    
    StartFn = 30
    EndFn = 130
    
        Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\huraa1.xlsb")
        Set wh = ActiveSheet
        Application.Workbooks.Open ("C:\Users\wolfgang\Documents\recording.xlsx ")
        Set wr = ActiveSheet
    For A = StartFn To EndFn
        Application.Workbooks.Open ("C:\Users\wolfgang\Documents\M" & A & ".xlsb ")
        Set ws = ActiveSheet
        wh.Range("A2:F94190").ClearContents
        ws.Range("A1:F94153").Copy: wh.Range("A2").PasteSpecial Paste:=xlPasteValues
        
        ws.Range("G1").Copy: wh.Range("BH2").PasteSpecial Paste:=xlPasteValues
        wh.Range("BE2:BH3").Copy
        wr.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        wh.Range("A2:F94190").ClearContents
        ws.Range("A94152:F174675").Copy
        wh.Range("A2").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        ws.Range("G1").Copy
        wh.Range("BH2").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        wh.Range("BE2:BH3").Copy
        wr.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        ws.Parent.Close SaveChanges:=False
    Next
            Calculate
    End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: want to speed up a loop algorithm

    This line works:

    
    Workbooks("Book1.xlsx").Sheets("sheet1").Range("A1") = Workbooks("Book3.xlsm").Sheets("sheet1").Range("A1").Value
    So Looking at your code, you can delete numerous lines :-

    
    Sub Get_Max_And_Min()
    Dim WB As Workbook
    Dim A As Long
    Dim StartFn As Long
    Dim EndFn As Long
    
    StartFn = 30
    EndFn = 130
    
        Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\huraa1.xlsb")
        Application.Workbooks.Open ("C:\Users\wolfgang\Documents\recording.xlsx ")
    
    For A = StartFn To EndFn
    
        Set WB = Application.Workbooks.Open("C:\Users\wolfgang\Documents\M" & A & ".xlsb ")
    
        Windows("huraa1.xlsb").Range("A2:F94190").ClearContents
    
        Windows("huraa1.xlsb").Range("A2:94154").End(xlUp).Offset(1).Value =    WB.Range("A1:F94153").Value
     
    Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to speed up loop code
    By notreallyIT in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-13-2013, 06:07 PM
  2. [SOLVED] Speed Up Loop in VBA
    By PY_ in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-19-2013, 08:57 AM
  3. [SOLVED] Help Speed up this Loop?!?
    By arleutwyler in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-06-2013, 01:49 AM
  4. Nested Loop problem/Algorithm
    By Asad74 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2012, 09:36 PM
  5. Trying to speed up the calculation my For Loop
    By Astroboy142 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2010, 04:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1