VBA Code to Hide and Unhide Rows

[tperkin1]

Thanks in advance for any help!

I am working in Excel 2003. I have a excel workbook with multiple sheets, and a sheet called Graph, which I want to contain a graph that automatically updates based on the information in range B7:F14. The information in this range is updated by a drop-down list in cell A1 which contains 11 different values. Some of the selections in this drop-down list will populate the entire range but some will not. When the range is not completely populated the legend of the graph contains extra symbols. Hiding and Unhiding the un-populated rows will eliminate the extra symbols in the legend. In range A7:A14 there is a formula that will either display "Hide" or "" based on if the rows contain information that needs to be in the graph. I am new to VBA, so any help creating a code for a Macro that will Hide and Unhide the rows 7-14 based on the formula result from A7:F14 would be greatly appreciated.
Thanks

 

[jbeaucaire]

1) Right-click the sheet tab and select View Code
2) Paste this "event" macro into the module that appears:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        For Each cell In Range("A7:A14")
            Rows(cell.Row).Hidden = cell = "HIDE"
        Next cell
    End If
    
End Sub

3) Close the VBEditor and save your workbook.

Now anytime you change cell "A1", cells A7:A14 will be checked and hidden/unhidden.

 

[tperkin1]

Thanks for the reply! The code is working, but now I have another question.

I don't want the users of the information to see the rows hide and unhide. So I moved the graph and the drop down list to a different worksheet (Graph Data).

So cell A1 is now a formula that is linked to the other worksheet (Graph Data). Once I changed cell A1 to a formula the code did not work. Is there a simple change that can be made to fix this?

 

[HalfAce]

Once I changed cell A1 to a formula the code did not work. Is there a simple change that can be made to fix this?

Yes. You change from using the Worksheet_Change event to using the Worksheet_Calculate event.

The change event gets invoked when a 'change' is made on the sheet somewhere. A formula calculating (or updating its return) is not considered a sheet change, because the formula didn't change, just the result did.

The drawback is that the code will get called every time any formula on the sheet calculates. This may or may not present a problem.

 

[jbeaucaire]

We could trick a Ws-Calc event to "sort of" operate like a "ws_change" and only execute its code if the value of A1 changes.

Option Explicit
Public MyVal As String

Private Sub Worksheet_Activate()
    If MyVal = "" And [A1] <> "" Then MyVal = [A1] & ""
End Sub


Private Sub Worksheet_Calculate()
Dim CELL As Range
    
    If [A1] <> MyVal Then
        For Each CELL In Range("A7:A14")
            Rows(CELL.Row).Hidden = CELL = "hide"
        Next CELL
        MyVal = [A1] & ""
    End If
    
End Sub

 

[tperkin1]

It worked, Thanks for the help!

 

[Goliath]

1) Right-click the sheet tab and select View Code
2) Paste this "event" macro into the module that appears:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        For Each cell In Range("A7:A14")
            Rows(cell.Row).Hidden = cell = "HIDE"
        Next cell
    End If
    
End Sub

3) Close the VBEditor and save your workbook.

Now anytime you change cell "A1", cells A7:A14 will be checked and hidden/unhidden.

This works if rows A7:A14 were hidden to begin with. My question is if the value in cell A1 reverts back to being blank, is it possible to revert back to hiding rows A7:A14?

My situation is I would like to hide rows 29:32 if the value in cell H29 is $0.00. If the value in cell H29 is anything other than $0.00, then I would like to have rows 29:32 displayed. Is this possible you think?

 

[jbeaucaire]

This is completely different.

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Rows("29:32").Hidden = Range("H29") = 0
    End If

Make the red section whatever test you want.