VBA Hide Row if cell = False, Unhide if = True, auto update

Fridays

New Member
Joined
Oct 27, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi All
I'm a novice and looking for help to fix and resolve this. I thought it would be straight forward but I've been going around in circles to get it to work.
I have sheet1 with check boxes (across columns) that informs sheet2 rows if they are checked (True) or un-checked (False).
On sheet2 I need the rows with a cell value of 'False' to be hidden, and 'True' to be unhidden. This needs to auto update when the cell changes (because a box is checked).
The True/False Data on Sheet2 is in Column H from Row 9:300:

Sub Hide_Rows_Based_On_Cell_Value()

StartRow = 9

EndRow = 300

ColNum = 4

For i = StartRow To EndRow

If Cells(i, ColNum).Value = "FALSE" Then

Cells(i, ColNum).EntireRow.Hidden = True

Else

Cells(i, ColNum).EntireRow.Hidden = False

End If

Next if

End Sub

*I also added a filter and tried to get it to auto update but I couldn't get this to work either.
Thanks in advance
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
OK, this will be my last effort tonight. In the absence of you, providing a copy of your file that I can use, I've put together a small demo file that you can adjust the range references etc to suit your actual file. I'm assuming your 'sheet1' and 'sheet2' are the actual code names (not tab names) of the 2 sheets involved.
Firstly, put this formula anywhere on sheet1:
Excel Formula:
=COUNTIF(E6:E8,FALSE)

Next, put this code in the code area of sheet1:
VBA Code:
Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim arr, c As Range, i As Long
    ReDim arr(1 To 3, 1 To 1)
    i = 1
    For Each c In Range("E6:E8")
        If c = True Then
            arr(i, 1) = c.Offset(, 1).Value
            i = i + 1
        End If
    Next c
    For i = 9 To 25
        If IsNumeric(Application.Match(Sheet2.Cells(i, 6).Value, arr, 0)) Then
            Sheet2.Rows(i).Hidden = False
            Else
            Sheet2.Rows(i).Hidden = True
        End If
    Next i
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Now when you check/uncheck a box in the range E6:E8 on sheet1, the values in column F (rows 9:25) on sheet2 are tested & those rows are either hidden or not - depending on the corresponding cells in column E on sheet1. Link to demo file: Fridays.xlsm
 
Upvote 0
1699621969235.png

I'm still plugging away at this :( I just need column A on Sheet2 to update if the 'Status' changes to true (if selected on Sheet1 the status updates to true and false if deselected).
I have this excel example but don't know how to upload it.
 
Upvote 0
I have this excel example but don't know how to upload it.
Could you share your file via Google Drive, Dropbox or similar file sharing platform? Just remember to make it available to anyone with the link.
 
Upvote 0
Thanks for that (y)
OK, here's a suggestion, based entirely on the file you shared. If your intention is that, whenever a checkbox is checked/unchecked on sheet 1, then the filtered list on sheet 2 changes automatically? If that's so, I've made some changes to your shared file.
1. Custom Formatted the cells linked to the checkboxes as ;;; which hides the underlying TRUE/FALSE text from view
2. Added a COUNTA() formula to sheet 2 in cell B1 (you can move this elsewhere & even hide the column you move it to)
3. Added a Worksheet_Calculate event module to sheet 2 that, whenever a change is made on sheet 1 (i.e. a checkbox status is changed) the COUNTA() formula will trigger the worksheet calculate event and 'reset' the autofilter on sheet 2.
Here's the link to the amended file: Bakery Version 2.xlsm
And here's the worksheet calculate event code:
VBA Code:
Private Sub Worksheet_Calculate()
    Me.AutoFilter.ApplyFilter
End Sub
 
Upvote 0
In hindsight, you don't even need the COUNTA() formula, due to the way you're returning the TRUE/FALSE from sheet 1. So you can scrub step 2 from the previous post. New link to the amended file: Bakery Version 2.xlsm
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top