Excel event change: hide or unhide rows

Question

I've been trying to run the below code, but whenever i change a value to 0 in column I, nothing happens.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim wb_afskrivninger As Workbook
Dim ws_afskrivningsberegning As Worksheet

Set wb_afskrivninger = ActiveWorkbook
Set ws_afskrivningsberegning = ThisWorkbook.Sheets("afskrivningsberegning")

Dim columnI_rowcount As Long

columnI_rowcount = ws_afskrivningsberegning.Range("I" & Rows.Count).End(xlUp).Row

Dim columnI_0count As Long

Application.ScreenUpdating = False

For columnI_0count = 0 To column_rowcount

    If ws_afskrivningsberegning.Range("I" & columnI_rowcount) = 0 Then
        Rows(columnI_rowcount).EntireRow.Hidden = True
        Else: Rows(columnI_rowcount).EntireRow.Hidden = False
    End If

Next columnI_0count

Application.ScreenUpdating = True

End Sub

Help would be appreciated.

Thanks in advance.

Why `Activeworkbook`? Where is this code placed? Where is the sheet `afskrivningsberegning` You need to use `Intersect`. [This](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) will get you started. — Siddharth Rout, May 16 '15 at 04:36
Alright thanks. The code is placed in the workbook i'm working in, so i guess there is no need to use Activeworkbook. — Sebastian Koefoed, May 16 '15 at 07:00
You can't "unhide" a row with your code, if it is already hidden, the code will not be able to see it. See my example to unhide the rows first before it loops through the range. — Davesexcel, May 16 '15 at 15:13

Davesexcel · Accepted Answer · 2015-05-16T15:10:26.710

Place it in the worksheet module, right click the sheet tab and select View Code, copy and paste this code there. Replace the msgbox line to whatever code you want to use when the target becomes 0

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 9 Then ' this is column "I"
        If Target = 0 Then
            MsgBox "You have changed the target value to Zero"
        End If
    End If
End Sub

If you were looking for a code to hide rows that = 0 then try this

Sub HideZeros()
    Dim Rws As Long, rng As Range, c As Range

    Columns("I:I").EntireRow.Hidden = False

    Rws = Cells(Rows.Count, "I").End(xlUp).Row
    Set rng = Range(Cells(1, "I"), Cells(Rws, "I")).SpecialCells(xlCellTypeConstants)

    For Each c In rng.Cells
        If c.Value = 0 Then c.EntireRow.Hidden = True
    Next c

End Sub

Excel event change: hide or unhide rows

1 Answers1