+ Reply to Thread
Results 1 to 9 of 9

Speed Up Loop in VBA

Hybrid View

  1. #1
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Speed Up Loop in VBA

    All,

    Is there a faster way to perform the following two loops?

        Dim i As Long
        Dim j As Long
    
            'Combine Project Number and Job Number
            For i = 1 To 100
                If Range("D" & i).Value = "" Then GoTo Nexti
                If Range("A" & i).Value = "" And Range("B" & i).Value = "" And Range("C" & i).Value = "" And Range("D" & i).Value <> "" Then
                    Range("D" & i - 2).Value = Range("D" & i).Value & " / " & Range("D" & i - 2).Value
                    End If
    Nexti:
                Next
    
            'Move Piece Counts + Frame Weight to job information row
            For j = 1 To 100
            If Range("A" & j).Value = "" Then GoTo Nextj
                If Range("A" & j).Value <> "" And Range("E" & j).Value <> "" Then
                    Range("G" & j).Value = Range("G" & j + 1).Value 'BuiltUp
                    Range("H" & j).Value = Range("H" & j + 1).Value 'HotRolled
                    Range("I" & j).Value = Range("I" & j + 1).Value 'ColdFab
                    Range("J" & j).Value = Range("J" & j + 1).Value 'HotDip
                    End If
    Nextj:
                Next
    Last edited by PY_; 07-19-2013 at 08:57 AM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Speed Up Loop in VBA

    perhaps
       Dim i As Long
    
       'Combine Project Number and Job Number
       For i = 1 To 100
          If Range("A" & i).Value = "" Then
             If Range("D" & i).Value <> "" Then
                If Range("B" & i).Value = "" And Range("C" & i).Value = "" Then _
                   Range("D" & i - 2).Value = Range("D" & i).Value & " / " & Range("D" & i - 2).Value
             End If
          Else
             If Range("E" & i).Value <> "" Then Range("G" & i).Resize(, 4).Value = Range("G" & i + 1).Resize(, 4).Value 'BuiltUp
          End If
       Next i
    have you already turned off screenupdating?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Speed Up Loop in VBA

    From what i can tell, you are combining the two loops into one and it only performs one thing else another thing. I need it to perform both loops separately because the second one depends on the first loop's changes.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Speed Up Loop in VBA

    Quote Originally Posted by PY_ View Post
    the second one depends on the first loop's changes.
    in what way?

  5. #5
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Speed Up Loop in VBA

    If i can do this without violating forum rules, i have a second set of loops that also need another eye to look at. I either run the above loop or the below loop depending on the import i am performing. (Last loop i promise)


    Dim i As Long
    Dim j As Long
    
    'Move piece counts to job information row
            For i = 1 To 100
                If Range("A" & i).Value = "" Then GoTo Nexti
                If Range("A" & i).Value <> "" And Range("E" & i).Value <> "" Then
                    Range("G" & i).Value = Range("G" & i + 1).Value
                    Range("H" & i).Value = Range("H" & i + 1).Value
                    Range("I" & i).Value = Range("I" & i + 1).Value
                    Range("J" & i).Value = Range("J" & i + 1).Value
                    End If
    Nexti:
                Next
                
           'Delete Rows with a blank cell in columnE
            Range("E1:E" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            
            'Move Order ammount up to same line as job information
            For j = 1 To 100
            If Range("A" & j).Value = "" Then GoTo Nextj
            If Range("A" & j).Value = "" And Range("B" & j).Value = "" And Range("E" & j).Value <> "" Then
                    Range("E" & j - 1).Value = Range("E" & j).Value
                    Range("E" & j).EntireRow.Delete
                    End If
    Nextj:
                Next

  6. #6
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Speed Up Loop in VBA

    Sorry, yes screen updating is off and so are events.

  7. #7
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Speed Up Loop in VBA

    Good question...haha

    For some reason, i had it in my head they were dependent on each other. I guess because the way i did it in my head is the way i did it in code and never looked back. I ran it your way and it did seem to cut the time in half.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Speed Up Loop in VBA

    perhaps
       Dim i As Long
       Dim j As Long
    
    'Move piece counts to job information row
       For i = 1 To 100
          If Range("A" & i).Value <> "" Then
             If Range("E" & i).Value <> "" Then Range("G" & i).Resize(, 4).Value = Range("G" & i + 1).Resize(, 4).Value
          End If
       Next i
       
       'Delete Rows with a blank cell in columnE
       Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
       
       'Move Order ammount up to same line as job information
       For j = 100 To 1 Step -1
          If Range("A" & j).Value = "" Then
             If Range("B" & j).Value = "" Then
             ' already deleted blank cells in E
    '            If Range("E" & j).Value <> "" Then
                   Range("E" & j - 1).Value = Range("E" & j).Value
                   Range("E" & j).EntireRow.Delete
    '            End If
             End If
          End If
       Next j

  9. #9
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Speed Up Loop in VBA

    That worked as well. It was missing some data when it went from 100 to 1 so i set it back to 1 to 100 and that did the trick.


    Thank you JosephP, that did cut down the runtime some and every second i can shave off definitely helps!

+ 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. [SOLVED] Help Speed up this Loop?!?
    By arleutwyler in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-06-2013, 01:49 AM
  2. Speed up a VLookUp loop
    By wizuriel in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-11-2013, 07:28 PM
  3. Loop alternatives or improvement for speed
    By TommyN in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2013, 06:00 PM
  4. Speed Up VBA Loop Code
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 12-30-2012, 07:20 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