title: Hide certain named ranges in another worksheet based on condition
body: <p>I am working on a code to hide named ranges in another worksheet based on a condition.</p>
  
  <p>I have a worksheet called "Committed Costs":<br>
  <img src="https://i.stack.imgur.com/RDEw1.png" alt="enter image description here">]<a href="https://i.stack.imgur.com/RDEw1.png" rel="nofollow noreferrer">1</a></p>
  
  <p>There will be many more lines at the bottom (I have a little over 7000 right now). Range A3:E14 is named cc_0101, range A14:E26 is named cc_0195. And there are many more below. </p>
  
  <p>I have a "Settings" worksheet:<br>
  <a href="https://i.stack.imgur.com/Rw6rf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Rw6rf.png" alt="enter image description here"></a></p>
  
  <p>Column C is a drop-down with two options: "Show" and "Hide". The formula in column D shows 1 if "Hide" is selected and 0 if "Show" is selected. Now I want the macro to go through the list of accounts on the "Settings" worksheet and if D8=1, then the whole cc_0101 named range from "Committed Costs" worksheet should be hidden, if D9=0, then cc_0195 should not be hidden and so forth. So basically, the macro now has to grab the value from column E for each row.</p>
  
  <p>I have the following code:</p>
  
  <pre><code>Dim showhide1 As Range
  Set showhide1 = ThisWorkbook.Worksheets("Settings").Range("d7:g716")
  Dim showhide2 As Variant
  showhide2 = showhide1.Value
  Dim i As Integer, j As Integer
  
  For i = LBound(showhide2, 1) To UBound(showhide2, 1)
    For j = LBound(showhide2, 2) To UBound(showhide2, 2)
      If showhide1(i, 1) = 1 Then
        Sheets("Committed Costs").Range("showhide2(i,2)").EntireRow.Hidden = True
      Else
        Sheets("Committed Costs").Range("showhide2(i,2)").EntireRow.Hidden = False
      End If
    Next j
  Next i
  </code></pre>
  
  <p>The code produces</p>
  
  <blockquote>
    <p>Run-time error 1004.</p>
  </blockquote>
  
  <p>Before I started reading about arrays and everything the code looked as follows:</p>
  
  <pre><code>Dim drng As Range, d as Range
  Dim erng As Range, e as Range
  
  Set drng = Range("D7:D716")
  Set erng = Range("E7:E716")
  
  For Each d In drng
    If d = 1 Then
      Sheets("Committed Costs").Range("e").EntireRow.Hidden = True
    Else
      Sheets("Committed Costs").Range("e").EntireRow.Hidden = False
    End If
  Next d
  </code></pre>
  
  <p>Then I realized that I am not defining "e" in any way, so the macro won't know to grab the value from column E (I am guessing). The only way I got the condition to work was:</p>
  
  <pre><code>Dim d as Range
  Set d = Range("D8")
  
  If d = 1 Then
    Sheets("Committed Costs").Range("cc_0101").EntireRow.Hidden = True
  Else
    Sheets("Committed Costs").Range("cc_0101").EntireRow.Hidden = False
  End If
  </code></pre>
  
  <p>but that doesn't serve the purpose. I have to hard-code the name of the range, which is cc_0101 in the example and the code only compares one line (row 8), instead of going through the whole list of accounts.</p>