none
Macro that hides rows based on a value runs very slow RRS feed

  • Question

  • I have a macro that hides rows if the value in column Q is = to 0.  It covers rows 4 to 5,000.  The macro takes forever to run.  I have already turned off ScreenUpdating, and this hasn't helped.   Then I tried breaking down the "FOR EACH" loop into 3 sections thinking maybe I am using too much memory.  This didn't help either. Anybody have any ideas on how I can get this to run faster?

    Here is the full macro:

    Sub ShowFlips()
    'The routine hides all rows except accts whose balance has flipped from a DR to a CR or from a CR to a DR.   Does nothing if there are none

    'Get the number of Flips
    Dim NoOfFlips As Integer
    NoOfFlips = Sheets("Grouping").Range("Q1")

    'If there are no flips, jump down to NO FLIPS and give message.
    If NoOfFlips = 0 Then
    MsgBox Prompt:="No Accts have flipped balances", Title:="This is a good thing"
    GoTo NothingToShow
    End If

    'In case rows are already hidden - unhide them all.  (they could be hidden if one of the other buttons was used and left that way)

    'Application.ScreenUpdating = False
        
        Sheets("Ungrouped Accts").Select
        ActiveSheet.Unprotect
        Rows("3:6000").EntireRow.Hidden = False
        ActiveSheet.Protect
        
        Sheets("Grouping").Select
        ActiveSheet.Unprotect
        Rows("3:5000").EntireRow.Hidden = False
        Range("A4").Select
        
        
    'This is where to hide all the Non Flip Rows
    MsgBox Prompt:="This will take X to Y minutes! Please be patient." & Chr(13) & Chr(13) & "You will be notified when the formatting is complete.", Title:="Large Task Started . . ."

    'Tried breaking the loop into 3 sections to see if it will run faster - didn't work
    Dim cell As Range
    For Each cell In Range("Y4:Y2000")   'formulas go to row 5000 so need to increase range if acct mapping exceed 5k rows are added,
    If cell.Value = 0 Then
    cell.EntireRow.Hidden = True
    End If
    Next

    For Each cell In Range("Y2001:Y4000")   'formulas go to row 5000 so need to increase range if acct mapping exceed 5k rows are added,
    If cell.Value = 0 Then
    cell.EntireRow.Hidden = True
    End If
    Next

    For Each cell In Range("Y4001:Y5000")   'formulas go to row 5000 so need to increase range if acct mapping exceed 5k rows are added,
    If cell.Value = 0 Then
    cell.EntireRow.Hidden = True
    End If
    Next

    Range("A4").Select
    ActiveSheet.Protect

    'Application.ScreenUpdating = True

    MsgBox Prompt:="Whew, that was a lot of rows to check but Excel is now done.", Title:="DONE !"
       
    NothingToShow:
    End Sub

    Thursday, October 6, 2016 6:16 PM

Answers

  • A base question : Why not using excel's autofilter. The criteria will be

    Range("Y2001:Y4000").autofilter field:=1,criteria1:="<>0"

    If there is sufficient reason for not using autofilter which is fastest for this job, then there can be one work around.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol


    Thursday, October 6, 2016 6:30 PM
    Answerer