Simple but stupid.

[basacwards]

Calling a VBA smarty pants for some help! I am still trying to learn VB and could use some help.

I am trying to write code to do the following:

If a cell changes in range B4:G20 I need to
Check D18:D50 and if one of those cells equals 0 or blank hide that row.

Everytime there is another change within B4:G20 unhide all rows and check again for 0 or blanks and hide.

Thanks in advance !!

 

[JoeMo]

This is sheet event code for the worksheet of interest.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("B4:G20")) Is Nothing Then
    Application.ScreenUpdating = False
    Range("D18:D50").EntireRow.Hidden = False
    For Each c In Range("D18:D50")
        If IsEmpty(c) Or c.Value = 0 Then c.EntireRow.Hidden = True
    Next c
End If
Application.ScreenUpdating = True
End Sub

 

[shg]

In the sheet module,

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim iRow          As Long

  If Not Intersect(Target, Me.Range("B4:G20")) Is Nothing Then
    For iRow = 18 To 50
      If Rows(iRow).Hidden And Not Val(Rows(iRow).Range("D1").Value) = 0 Then Rows(iRow).Hidden = False
      If Not Rows(iRow).Hidden And Val(Rows(iRow).Range("D1").Value) = 0 Then Rows(iRow).Hidden = True
    Next iRow
  End If
End Sub

That preserves Undo if no row visibility needs to change.

 

[basacwards]

Thanks for the quick help!

 

[basacwards]

What if i was to complicate it slightly. Hide the rows between 18-50 if 0 ...BUT... never hide rows 22-24 ever.

 

[JoeMo]

In the sheet module,

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim iRow          As Long

  If Not Intersect(Target, Me.Range("B4:G20")) Is Nothing Then
    For iRow = 18 To 50
      If Rows(iRow).Hidden And Not Val(Rows(iRow).Range("D1").Value) = 0 Then Rows(iRow).Hidden = False
      If Not Rows(iRow).Hidden And Val(Rows(iRow).Range("D1").Value) = 0 Then Rows(iRow).Hidden = True
    Next iRow
  End If
End Sub

That preserves Undo if no row visibility needs to change.

shg - is there a way to preserve undo if the change event is the deletion of an existing cell entry in the target range?

 

[shg]

No, Joe. Virtually any change to the workbook will flush Undo.

 

[JoeMo]

No, Joe. Virtually any change to the workbook will flush Undo.

Thanks for that info.