none
Speed up Row-hiding macro RRS feed

  • Question

  • Hi All.........

    Please advise if there is a way to speed up the following macro.  On my database it takes about 2 1/2 minutes to run....much too long.

    Sub HideRowIfCompleted()
    Dim LastRow As Long, r As Long
    Sheets("WorkOrders").Select
    LastRow = Cells(Rows.Count, "i").End(xlUp).Row
    Range("a3").Select
    Application.ScreenUpdating = False
      For r = LastRow To 12 Step -1
          If Cells(r, "Au") <> "" Then
          Cells(r, "au").EntireRow.Hidden = True
          End If
      Next r
      Application.ScreenUpdating = True
    End Sub

    TIA for any help,

    Chuck, CABGx3


    Chuck, CABGx3

    Sunday, August 21, 2016 7:25 PM

Answers

  • Try this version. It assumes that cell AU11 is not empty, otherwise it won't work as intended.

    Sub HideRowIfCompleted()
        Dim LastRow As Long
        Application.ScreenUpdating = False
        With Worksheets("WorkOrders")
            LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            .Range("AU11:AU" & LastRow).AutoFilter Field:=1, Criteria1:="="
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Monday, August 22, 2016 1:37 PM
    Sunday, August 21, 2016 10:05 PM
  • As in my previous reply, the following will only work correctly if there is something in row 11 in the relevant column, in this case in cell AR11.

    Sub HideRowPerDaterange()
        Dim StartDate As Long
        Dim EndDate As Long
        Dim LastRow As Long
        With Worksheets("MainMenu")
            StartDate = .Range("H20").Value
            EndDate = .Range("K20").Value
        End With
        Application.ScreenUpdating = False
        With Worksheets("WorkOrders")
            LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            .Range("AR11:AR" & LastRow).AutoFilter _
                Field:=1, _
                Criteria1:=">=" & StartDate, _
                Operator:=xlAnd, _
                Criteria2:="<=" & EndDate
        End With
        Application.ScreenUpdating = True
    End Sub

    Note that StartDate and EndDate have been defined as Long to force Excel to use the underlying value of the cells, regardless of date format.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Monday, August 22, 2016 3:28 PM
    Monday, August 22, 2016 2:29 PM
  • A worksheet can contain only a single AutoFilter range, so we must apply all filters to the same range (but to different columns). Try this version:

    Sub HideRowsMultipleCriteria()
        Dim StartDate As Long
        Dim EndDate As Long
        Dim LastRow As Long
        With Worksheets("MainMenu")
            StartDate = .Range("H20").Value
            EndDate = .Range("K20").Value
        End With
        Application.ScreenUpdating = False
        With Worksheets("WorkOrders")
            LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            .Range("AR11:AU" & LastRow).AutoFilter _
                Field:=1, _
                Criteria1:=">=" & StartDate, _
                Operator:=xlAnd, _
                Criteria2:="<=" & EndDate
            .Range("AR11:AU" & LastRow).AutoFilter _
                Field:=2, _
                Criteria1:="="
            .Range("AR11:AU" & LastRow).AutoFilter _
                Field:=4, _
                Criteria1:="="
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Tuesday, August 23, 2016 12:10 AM
    Monday, August 22, 2016 8:56 PM

All replies

  • Try this version. It assumes that cell AU11 is not empty, otherwise it won't work as intended.

    Sub HideRowIfCompleted()
        Dim LastRow As Long
        Application.ScreenUpdating = False
        With Worksheets("WorkOrders")
            LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            .Range("AU11:AU" & LastRow).AutoFilter Field:=1, Criteria1:="="
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Monday, August 22, 2016 1:37 PM
    Sunday, August 21, 2016 10:05 PM
  • Hi Hans.......

    As usual, your solution works PERFECTLY!.....Many thanks.

    It was also clear enough that I was able to modify it to work to replace another one of my Loop macros, but making it work for the one below is beyond my skill level.  If you would be so kind when you get a chance, I would much appreciate.......

    Thanks,

    Chuck, CABGx3

    Sub HideRowPerDaterange()
    '=============================================================================
    'Macro hides all rows of WorkOrders database that are NOT within the daterange on MainMenu
    '====================================================================================
    Dim LastRow As Long, r As Long
    Dim StartDate
    Dim EndDate

    StartDate = Worksheets("MainMenu").Range("H20").Value
    EndDate = Worksheets("MainMenu").Range("K20").Value

        Sheets("WorkOrders").Select
        Range("a3").Select
        Application.ScreenUpdating = False
        LastRow = Cells(Rows.Count, "i").End(xlUp).Row
            For r = LastRow To 12 Step -1
                  '  Cells(r, "Ar").Select
                  If Cells(r, "Ar") < StartDate Or Cells(r, "AR") > EndDate Then
                  Cells(r, "au").EntireRow.Hidden = True
                  '  Cells(r, "A").Select
                  End If
            Next r
          Application.ScreenUpdating = True
          
    End Sub


    Chuck, CABGx3

    Monday, August 22, 2016 1:37 PM
  • As in my previous reply, the following will only work correctly if there is something in row 11 in the relevant column, in this case in cell AR11.

    Sub HideRowPerDaterange()
        Dim StartDate As Long
        Dim EndDate As Long
        Dim LastRow As Long
        With Worksheets("MainMenu")
            StartDate = .Range("H20").Value
            EndDate = .Range("K20").Value
        End With
        Application.ScreenUpdating = False
        With Worksheets("WorkOrders")
            LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            .Range("AR11:AR" & LastRow).AutoFilter _
                Field:=1, _
                Criteria1:=">=" & StartDate, _
                Operator:=xlAnd, _
                Criteria2:="<=" & EndDate
        End With
        Application.ScreenUpdating = True
    End Sub

    Note that StartDate and EndDate have been defined as Long to force Excel to use the underlying value of the cells, regardless of date format.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Monday, August 22, 2016 3:28 PM
    Monday, August 22, 2016 2:29 PM
  • Hi Hans......

    Back again here........

    Your second solution of course worked perfectly for the conditions I gave you, and now that I've wasted your time, your solutions have come to make it clear to me what I really need.

    Your substitution of the Autofilter feature in place of one of my Loops, works excellently when used one at a time at I gave the problem to you.  

    However, in my main macro I actually use THREE loops for the deletion of rows.  I thought I could just modify your Autofilter solutions for each of my three loops and my whole thing would work..........not so fast Chuck!  When ONE of the Autofilter solutions runs, the system kinda LOCKS the Autofilter feature in place on that one column and I can't seem to be able to run another criteria on another column.

    Your last solution that gives TWO criteria is fine, but I need to somehow be able to change columns for other criteria in  two other columns.

    I want only BLANKS instead of dates to show in Col AU

    I also want only BLANKS instead of "x" to show in Col AS

    And finally I want only those dates within my daterange to show in Col AR

    Is this possible, or do I need to refer back to using one Autofilter and two Loops?

    Thanks,

    Chuck, CABGx3


    Chuck, CABGx3

    Monday, August 22, 2016 3:28 PM
  • A worksheet can contain only a single AutoFilter range, so we must apply all filters to the same range (but to different columns). Try this version:

    Sub HideRowsMultipleCriteria()
        Dim StartDate As Long
        Dim EndDate As Long
        Dim LastRow As Long
        With Worksheets("MainMenu")
            StartDate = .Range("H20").Value
            EndDate = .Range("K20").Value
        End With
        Application.ScreenUpdating = False
        With Worksheets("WorkOrders")
            LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            .Range("AR11:AU" & LastRow).AutoFilter _
                Field:=1, _
                Criteria1:=">=" & StartDate, _
                Operator:=xlAnd, _
                Criteria2:="<=" & EndDate
            .Range("AR11:AU" & LastRow).AutoFilter _
                Field:=2, _
                Criteria1:="="
            .Range("AR11:AU" & LastRow).AutoFilter _
                Field:=4, _
                Criteria1:="="
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Tuesday, August 23, 2016 12:10 AM
    Monday, August 22, 2016 8:56 PM
  • As usual, a perfect solution from Hans Vogelaar.

    Your code is a work of art and such a pleasure to use.  I can actually read it, and because it's written so clearly, I can even sometimes modify it slightly for other uses.

    I really appreciate you taking the time to help me.  I've learned a lot this time.

    Regards,

    Chuck, CABGx3


    Chuck, CABGx3

    Monday, August 22, 2016 10:43 PM
  • Another nice one Hans!

    Nothing is faster than AutoFilter!


    MY BOOK

    Wednesday, August 24, 2016 12:33 PM