0

enter image description here

I am trying to hide the Rows that are false.

Currently I have

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Range("B10:B13") = False Then
        Target.EntireRow.Hidden = True
    End If
End Sub
Michael Downey
  • 687
  • 3
  • 13
  • 42

2 Answers2

2

Try iterating over each cell in the range and testing its value like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim c As Range
    For Each c In Range("B9:B13")
        If Not c.Value Then
            c.EntireRow.Hidden = True
        End If
    Next c
End Sub

Edit: I think another way you can achieve the result you are after is to remove the series from the chart

Dim n As Long
With ChartObjects(1).Chart
   For n = .SeriesCollection.Count To 1 Step -1
      With .SeriesCollection(n)
          If .Name = "" Then
              .Delete
          End If
      End With
   Next n
End With
T I
  • 9,785
  • 4
  • 29
  • 51
  • Would I just Add Else C.EntireRow.Hidden = False Assuming I wanted it be be visible, when I change it to True – Michael Downey Jun 27 '13 at 16:04
  • Why do I have to click under the True False to get it to change? – Michael Downey Jun 27 '13 at 16:24
  • 1
    @MichaelDowney re. adding `else ...` then yes if you need them to reshow them. re. clicking under this is because it is only when you leave the cell, confirming the entry, that the worksheet has changed. You could try [capturing keypress events](http://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell) – T I Jun 27 '13 at 16:46
  • Thanks! Hmm you would not happen to have simpler way to remove the blank from the Legend Key would you? Since that was the reason for making the row hidden initially. – Michael Downey Jun 27 '13 at 16:56
2
for i = 9 to 13
if range("B" & i) = "False" then
    Rows(i & ":" & i).EntireRow.Hidden = True
end if
next
Ed Heywood-Lonsdale
  • 108
  • 6