none
VBA code to speed up macro RRS feed

  • Question

  • Hello! I would like to consult for a recommendation on this macro. It runs successfully but the problem is, it is too slow and sometimes completes after 5 minutes. Thanks in advance for your recommendations. 

    Application.ScreenUpdating = False ' turns screen updating off - won't show flashing screens
    Application.DisplayAlerts = False
    relativePath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        
    endOfSheet = ActiveSheet.UsedRange.Rows.count
    
     'Delete entire row if it shows "1" in Col F
     Last = Cells(Rows.count, "A").End(xlUp).row
        For i = Last To 1 Step -1
            If (Cells(i, "F").Value) = "1" Then
                Cells(i, "A").EntireRow.Delete
            End If
        Next i
        
    
        
      'Delete entire row if NAs are in Col D
        With ActiveSheet
            On Error Resume Next
            With .Columns(4).SpecialCells(xlCellTypeFormulas, xlErrors)
                .EntireRow.Delete
            End With
            With .Columns(4).SpecialCells(xlCellTypeConstants, xlErrors)
                .EntireRow.Delete
            End With
            On Error GoTo 0
        End With
       
    
    msgBox ("DONE")


    • Edited by IamJackie Tuesday, February 6, 2018 10:58 PM
    Tuesday, February 6, 2018 10:58 PM

Answers

  • I,
    re:  code is slow

    Deleting rows is slow
    It is the bottom of column F you need not column A.
    Looping thru an array is faster than doing a range.
    Sorting is very fast.
    Some air code...
    '---
    Sub ImproveCode()
     Dim Fvalues As Variant
     Dim Last As Long
     Dim N As Long

     Last = Cells(Rows.Count, 6).End(xlUp).Row
     Fvalue = Range(Cells(1, 6), Cells(Last, 6)).Value
       For N = Last To 1
        If Fvalue(N, 1) = 1 Then Rows(N).Clear
       Next
      'Now sort the data range; blanks go to the bottom
    End Sub
    '---
    Jim Cone
    https://goo.gl/IUQUN2    (Dropbox)

     
    • Marked as answer by IamJackie Wednesday, February 7, 2018 3:39 PM
    Wednesday, February 7, 2018 12:02 AM
  • Hi IamJackie,

    I can see that You already set ScreenUpdating to false.

    It is possible that when you delete each row , it will trigger formula to recalculate. which may increase the execution time.

    You can try to change calculation to manual.

    Application.Calculation = xlManual
    
    '------Execute your code
    
    Application.Calculation = xlAutomatic

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by IamJackie Wednesday, February 7, 2018 3:39 PM
    Wednesday, February 7, 2018 3:06 AM
    Moderator

All replies

  • I,
    re:  code is slow

    Deleting rows is slow
    It is the bottom of column F you need not column A.
    Looping thru an array is faster than doing a range.
    Sorting is very fast.
    Some air code...
    '---
    Sub ImproveCode()
     Dim Fvalues As Variant
     Dim Last As Long
     Dim N As Long

     Last = Cells(Rows.Count, 6).End(xlUp).Row
     Fvalue = Range(Cells(1, 6), Cells(Last, 6)).Value
       For N = Last To 1
        If Fvalue(N, 1) = 1 Then Rows(N).Clear
       Next
      'Now sort the data range; blanks go to the bottom
    End Sub
    '---
    Jim Cone
    https://goo.gl/IUQUN2    (Dropbox)

     
    • Marked as answer by IamJackie Wednesday, February 7, 2018 3:39 PM
    Wednesday, February 7, 2018 12:02 AM
  • Hi IamJackie,

    I can see that You already set ScreenUpdating to false.

    It is possible that when you delete each row , it will trigger formula to recalculate. which may increase the execution time.

    You can try to change calculation to manual.

    Application.Calculation = xlManual
    
    '------Execute your code
    
    Application.Calculation = xlAutomatic

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by IamJackie Wednesday, February 7, 2018 3:39 PM
    Wednesday, February 7, 2018 3:06 AM
    Moderator
  • Thank you so much! Worked perfectly!
    Wednesday, February 7, 2018 3:40 PM
  • Thank you so much! Definitely helped in speeding up the process! 
    Wednesday, February 7, 2018 3:40 PM