title: VBA - Speed of Hiding/Unhiding Row as a Worksheet Event
body: <p>I'm struggling with the speed at which the following VBA code executes. </p>
  
  <p>The goal of this code is to activate whenever "C4" changes, and then scan column "R" for the value 'Y'. If there's a 'Y', then it hides the row, and if not, it unhides the row. The code works, it's just not speedy - for 500 rows, it can take 30 or more seconds every time I change the value of "C4". </p>
  
  <p>Does anyone have any suggestions to improve the speed at which this code executes? Or another method of accomplishing this?</p>
  
  <p>Thanks for taking a look.</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim L As Long
  Dim r As Range
  
  L = Cells(Rows.Count, "R").End(xlUp).Row
  
  If Not Intersect(Target, Range("C4")) Is Nothing Then
      For Each r In Range("R2:R" &amp; L)
          If r.Value = "Y" Then
              Rows(r.Row).Hidden = True
          Else
              Rows(r.Row).Hidden = False
          End If
      Next
  End If
  
  End Sub
  </code></pre>
  
  <p>In attempting to apply the suggestion below - use Union() - I have come up with the below, not working, code. Any help would be greatly appreciated. </p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim L As Long
  Dim r As Range
  Dim RowsToHide As Range
  Dim RowsToUnhide As Range
  
  L = Cells(Rows.Count, "R").End(xlUp).Row
  
  If Not Intersect(Target, Range("C4")) Is Nothing Then
      For Each r In Range("R2:R" &amp; L)
          If r.Value = "Y" Then
              RowsToHide = Union(RowsToHide, r.Row)
          Else
              RowsToUnhide = Union(RowsToUnhide, r.Row)
          End If
      Next
  End If
  
  RowsToHide.Hidden = True
  RowsToUnhide.Hidden = False
  
  End Sub
  </code></pre>