code for hiding unhiding rows

[chobanne]

Can someone please help me to make this code for my sheet.

I have cell A1 which is actually data validation list. When i choose some value in that cell, the values in column B changes in a way that some values becomes 0 some becomes 1. Thats are formula connection values in column B

I need a code that will hide me every rows with value 1 in colum B and unhide me every rows with value 0 in column B, every single time when i choose another value in A1.

Actually i can do this with filter command but when i choose another value in cell A1 i must do manually filtering again, and again etc.

Thank you

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in teh VBG Editor window that pops up and see if that does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long, r As Long
    
'   Exit it cell A1 not updated
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    
'   Find last row with values in column B
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
'   Loop through all rows
    For r = 1 To lr
        If Cells(r, "B").Value = 0 And Len(Cells(r, "B")) = 1 Then
            Rows(r).EntireRow.Hidden = False
        Else
            If Cells(r, "B").Value = 1 Then
                Rows(r).EntireRow.Hidden = True
            End If
        End If
    Next r

    Application.ScreenUpdating = True

End Sub

It should run every time you update the value in cell A1.

 

[chobanne]

Thank you that is doing what i want

 

[Joe4]

Excellent!
Glad I was able to help!