0

I have a dropdown list in cell D3. Once you change D3 the rows values changes some with numbers over/under 0 and some remain a sharp 0 in column I. I need a Macro which will loop and the result should be that when I change D3, all information with exact 0 value in column I will be hidden, BUT when I change D3 again it will reset and again hide all rows which include a 0 in column I.

Dorian
  • 1
  • 1
    Welcome to Super User! Please note that https://superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read [ask]. – DavidPostill Mar 16 '21 at 08:40

1 Answers1

0

To trigger a macro with changes on a sheet, you can put a sub on the worksheet, using the
Worskheet.Change event : Private Sub Worksheet_Change(ByVal Target As Range)

For looping the "I" Column, there are a few ways to loop through it. We can define the range, and do a For each loop, or we can use an iterable.

To avoid looping the entire column, we can find the last use row of the column in a few different ways, Cells(Rows.Count, 9).End(xlUp).Row is one of them.

Checking the value of the cell in the loop is straight forward enough and to hide the row, we can just use Range.EntireRow.Hidden = True

The whole thing could look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim hide As Boolean, i As Long
'Intersect validates that the change is only happening in "D3" to be valid
If Not Intersect(Target, Range("D3")) Is Nothing And Target.Count = 1 Then
' i = starting range - to - last row    
    For i = 4 To Cells(Rows.Count, 9).End(xlUp).Row
        hide = False
        If Cells(i, 9).Value = "0" Then hide = True
        Cells(i, 9).EntireRow.Hidden = hide
    Next i
End If
End Sub
Christofer Weber
  • 1,313
  • 1
  • 8
  • 14