dBforums - View Single Post

Colin Legg · #11 (**permalink**) 12-07-09, 06:35

In that case we need the Worksheet_Change event handler.

Right click on the worksheet tab and choose View Code. This will open up the worksheet's class module in the VBE. Add in this code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Const strMATRIX As String = "C4:G6", strFORMULAS As String = "B12:B34"
    Dim rngCell As Range
    
    On Error GoTo ErrorHandler
    
    'was a cell in the matrix changed?
    If Not Intersect(Target, Range(strMATRIX)) Is Nothing Then
        Application.ScreenUpdating = False
        
        'calculate the formula range in case user has calcs set to manual
        Range(strFORMULAS).Calculate
        
        For Each rngCell In Range(strFORMULAS).Cells
            rngCell.EntireRow.Hidden = (LenB(rngCell.Value) = 0)
        Next rngCell
    End If
    
CleanUp:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    MsgBox "error number: " & Err.Number & vbNewLine & "error message: " & Err.Description
    Resume CleanUp
End Sub

Rather than using string constants for the two ranges you could use named ranges.

Hope that helps...