title: Hide 1 row above and 8 rows below a cell which changes based on picklist from another sheet
body: <p>I'm trying to automate a reporting form. Text values selected from from a validation dropdown list on Sheet1 designate (by way of formulae) whether or not cells on Sheet9 have a value of "" or a concatenated hybrid of values entered on the first sheet.</p>
  
  <p>Sheet9 basically contains 200 blank tables (including rows in between, 9 rows per table) and the cells we are concerned with give each table a title to signify that they need to be completed. Any table without a title present should be hidden.  </p>
  
  <p>I have named a Range "JanRange" which is just the title cells, I was hoping to loop to each target title cell, evaluate whether or not it is displaying a value and if not, hide the block of rows from 1 row above to 8 rows below.</p>
  
  <p>I've looked all over the place but anything I try to run doesn't seem to work (I think the main problem I've had is getting the code for Sheet9 to run based on the cell on Sheet9 changing via a formula dependent on a change from Sheet1)</p>
  
  <p>If you need me to provide more detail, please just ask!</p>
  
  <p>Many thanks,</p>
  
  <p>DuNk</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>So I've now got the following code which will successfully hide the row of each title cell in my named range:</p>
  
  <pre><code>Sub Worksheet_Calculate()
  Application.ScreenUpdating = False
  Dim Cell As Range
     For Each Cell In Range("TestRange")
        If Cell.Value = "" Then
        Cell.EntireRow.Hidden = True
        Else: Cell.EntireRow.Hidden = False
        End If
     Next
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>Which is great! But I need to extend what is hidden to the block of nine rows (1 above and 8 below) rather than just the single row. Any ideas?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Just tried the code below but am getting a runtime error 1004: Application-defined or object-defined error..</p>
  
  <pre><code>Sub Worksheet_Calculate()
  Application.ScreenUpdating = False
  Dim cell As Range
      For Each cell In Range("TestRange")
          If cell.Value = "" Then
          cell.Offset(-1, 1).Resize(8, 0).Select
          Selection.EntireRow.Hidden = True
          Else: cell.Offset(-1, 1).Resize(8, 0).Select
          Selection.EntireRow.Hidden = False
          End If
      Next
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Okay I think I've got there with the following:</p>
  
  <pre><code>Sub Worksheet_Activate()
  Application.ScreenUpdating = False
      Dim rngMyRange As Range
      Dim cell As Range
              Dim x As Integer
              Dim y As Integer
      Set rngMyRange = Range("TestRange")
      For Each cell In rngMyRange.Cells
          If cell.Value = "" Then
                                      x = cell.Row - 1
                                      y = cell.Row + 6
              Rows("" &amp; x &amp; ":" &amp; "" &amp; y &amp; "").Select
              Selection.EntireRow.Hidden = True
          Else:                       x = cell.Row - 1
                                      y = cell.Row + 6
              Rows("" &amp; x &amp; ":" &amp; "" &amp; y &amp; "").Select
              Selection.EntireRow.Hidden = False
          End If
      Next cell
  Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>It doesn't work with Worksheet_calculate() for some reason but being as data entry is on a different tab, going with Worksheet_activate() works just fine :)</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p><strong>Okay having now got this into my full workbook rather than the small scale test version, having the code running on 200 cells in JanRange is taking somewhere in the region of 10 seconds. There's no way I want this stuck to Worksheet_calculate or even worksheet_activate. I've tried associating it to a form control button on Sheet1 but it doesn't work..</strong></p>