Using Union to Build a Range and Hide Rows

[Brandon81]

What I am trying to achieve here with a certain portion of this code is to identify all the rows that have data in them in which a condition is or isn't met and then build a range using union and then hiding rows. The reason that I am trying to use union is because with what I had before I was going through each row individually (whether it had data or not) and then hiding them based on a condition. This took forever ( data has over 40K rows), and I think that using union to build two ranges and then hide the rows in which a certain condition is met/or not could make that much faster but I don't know how to set that up properly in this case as you can clearly see. Hopefully, the end result will be when user selects 'Unsecured Streamline' from a drop down list then some columns will be hidden as well as all the rows that don't have an 'AP' in column R. I appreciate any guidance here as I am trying to learn VBA.

Here is the part I am trying to work through:

 For Each cell In Me.Range("R3", .Range("R" & .Rows.Count).End(xlUp))
             If cell.Text <> "AP" Then
                    Set AP = NotAP
                Else
                    Set AP = Union(AP, NotAP)
                End If
            Next cell
            NotAP.EntireRow.Hidden = True

Here is the full code currently:

Private Sub Worksheet_Change(ByVal Target As Range)
 With Worksheets("Detail")
  
  Application.EnableEvents = False
  
  Dim AP As Range, NotAP As Range
   If Target.Address = ("$D$1") Then
        If Target.Text = "ALL" Then
            Range("A:DD").EntireColumn.Hidden = False
        
        ElseIf Target.Text = "OVERRIDES" Then
            Range("A:DD").EntireColumn.Hidden = False
            Range("A:C,J:O,AG:AQ,AX:AY,BA:BA,BC:BD,BF:BH,BJ:BK,BP:BQ,BT:BT,BW:BZ,CA:CB,CC:CI,CK:CK,CQ:CQ,CT:DD").EntireColumn.Hidden = True
       
        ElseIf Target.Text = "RATES" Then
            Range("A:DD").EntireColumn.Hidden = False
            Range("A:C,J:O,P:P,AB:AE,AG:AG,AR:AW,BE:BF,BW:CB,CE:CI,CK:CN,CR:CS,DB:DD").EntireColumn.Hidden = True
       
        ElseIf Target.Text = "UNSECURED STREAMLINE" Then
            Range("A:DD").EntireColumn.Hidden = False
            Range("AA:AM").EntireColumn.Hidden = True
            For Each cell In Me.Range("R3", .Range("R" & .Rows.Count).End(xlUp))
                 If cell.Text <> "AP" Then
                    Set AP = NotAP
                Else
                    Set AP = Union(AP, NotAP)
                End If
            Next cell
            NotAP.EntireRow.Hidden = True
            
       End If
  End If
  Application.EnableEvents = True
 End With
End Sub

 

[Fluff]

Try it like

            For Each cell In Me.Range("R3", .Range("R" & .Rows.Count).End(xlUp))
                 If cell.Text <> "AP" Then
                    if AP is nothing then Set AP = cell Else set AP=union(AP,cell)
                End If
            Next cell
            if not AP is nothing then AP.EntireRow.Hidden = True

 

[Brandon81]

Try it like

            For Each cell In Me.Range("R3", .Range("R" & .Rows.Count).End(xlUp))
                 If cell.Text <> "AP" Then
                    if AP is nothing then Set AP = cell Else set AP=union(AP,cell)
                End If
            Next cell
            if not AP is nothing then AP.EntireRow.Hidden = True

That worked that took me down from at least an hour to 5 minutes to run! I also tried hiding all rows and then unhiding only the ones I wanted visible (Not sure I did this the most efficient way) and doing this took me down to 74 seconds to run. Is there any further way to optimize that portion of code to complete even faster?

ElseIf Target.Text = "UNSECURED STREAMLINE" Then
            Rows("3:40000").EntireRow.Hidden = True
            Range("A:DD").EntireColumn.Hidden = False
            Range("AA:AM").EntireColumn.Hidden = True
            For Each cell In Me.Range("R3", .Range("R" & .Rows.Count).End(xlUp))
                    If cell.Text = "AP" Then
                         If AP Is Nothing Then Set AP = cell Else Set AP = Union(AP, cell)
                   End If
            Next cell
            If Not AP Is Nothing Then AP.EntireRow.Hidden = False

 

[Fluff]

You could turn Calculation & screen updating off, which may make a difference.