How do I hide rows based on cell values when closing the workbook

[RPeck]

I would like to hide the entire row when the value in column G for that row is equal to "Yes". I only want this macro to run for the open sheet and I would like it to run when the workbook is closed. I have seen similar requests, but I cannot make them work for me. All help is appreciated.
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Luke M]

Hi RPeck, and welcome to the forum.

In the future, it's helpful to provide the password to unlock the sheets, which is needed in order for macro to run. I didn't bother to crack it, so this code is untested but should work. Make sure you change the myPass variable to the real password.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Dim c As Range
Dim rngItems As Range
Dim saveState As Boolean
'IMPORTANT!!! Put the sheet protection password here
Const myPass As String = "abc123"

'Which worksheet to deal with?
Set ws = ActiveSheet

'Note if we should auto save, or user cancelled
saveState = ThisWorkbook.Saved

Application.ScreenUpdating = False
With ws
    .Unprotect myPass
    'Look for cells that have been filled in and are visible
    On Error Resume Next
    Set rngItems = .Range("G1:G" & .Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
   
    If Not rngItems Is Nothing Then
        For Each c In rngItems
            c.EntireRow.Hidden = (UCase(c.Value) = "YES")
        Next c
    End If
    .Protect myPass
End With

If saveState Then
    ThisWorkbook.Save
End If

End Sub

 

[RPeck]

Thank you for your help, but it is not hiding my rows. My password is NFSGI.

Thanks again.

 

[Chihiro]

The code has no issues. I think you might have it in wrong place (I.E. in standard module instead of workbook).

First, go to VBA Project and remove any unnecessary Modules.

Then double click on "ThisWorkbook"


Paste in Luke's code and change the password.

 

[RPeck]

This worked perfectly. Thank you so much!