Hide/Unhide Rows Based on Cell Value

[BruceJ]

Hi All,

First post here. I'm an experienced Excel user (former Lotus user) and just beginning to attempt some VBA. Most of what I've learned (which isn't much yet) has come from this board. At this point, what I know is pretty much limited to copy/paste of what seems a reasonable match and editing as needed. Honestly, I don't really know enough yet to formulate coding from scratch.

That said, I know WHAT I want to do....just not HOW.

The worksheet I have has 2 sheets. Sheet1 feeds some data to Sheet2.

In Sheet2, Column F (a hidden column) is either 1 or blank and is populated from Sheet1. I want to Hide all rows in Sheet2 that do not have 1 in column F. The event should run at any change to Sheet1.

Thanks in advance for any help.

Bruce

 

[BruceJ]

Bump

 

[BruceJ]

Filter would be a good option if it updated as the user made changes. The challenge with filter is that when the filter is applied, it does not update automatically as values in the filtered data changes.

 

[Fluff]

Hia
Try this

Private Sub Worksheet_Change(ByVal Target As Range)

    With Sheets("Sheet2").Columns("F")
        .AutoFilter
        .AutoFilter field:=1, Criteria1:="=1", VisibleDropDown:=False
    End With

End Sub

This needs to be added to the sheet module.
Right click on sheet1 tab select view code & paste the above into the code window

 

[BruceJ]

Hia
Try this

Private Sub Worksheet_Change(ByVal Target As Range)

    With Sheets("Sheet2").Columns("F")
        .AutoFilter
        .AutoFilter field:=1, Criteria1:="=1", VisibleDropDown:=False
    End With

End Sub

This needs to be added to the sheet module.
Right click on sheet1 tab select view code & paste the above into the code window

Thanks Fluff! That works.

This is going to be used by a number of users. The downside I see in using Filter is that clicking the Filter button in the toolbar unfilters everything until the enter something in Sheet1 again. That's why I was thinking that Hide Row may be a solution to that. I'm not sure though.

I really appreciate your help.

Bruce

 

[Fluff]

Another option is

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long
    
    With Sheets("Sheet2")
        For i = 2 To .Range("F" & Rows.Count).End(xlUp).Row
            If .Range("F" & i) <> 1 Then .Rows(i).Hidden = True
        Next i
    End With

End Sub

But this could slow things down a lot if you have a lot of rows

 

[BruceJ]

There's about 1500 rows in Sheet2 so you're right....it bogs down with each change to Sheet1.

 

[Fluff]

Hello Bruce

The downside I see in using Filter is that clicking the Filter button in the toolbar unfilters everything until the enter something in Sheet1 again.

Whilst this is true, there is nothing to actually show that a filter is in place. The column being filtered is hidden as is the filter button.
Also, with the second approach, there is nothing to stop a user from simply unhiding the hidden rows