VBA Macro to Hide/Unhide Rows Based on Criteria

[TWienholz]

I know very little VBA, but from what I've read it is the only way to automatically hide rows in a worksheet. I am attempting to do the following:

The cell Q9 in the current worksheet is determined by data from 'ProjectDataSheet'!J43 and assigns the text according to that cell remaining blank ("CORE SURVEY ONLY – NO CQs") or being >0 ("CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT"). The current worksheet is where I want to hide the non-applicable rows so that,

If Q9="CORE SURVEY ONLY – NO CQs", then I want rows A35:A61 to be hidden; HOWEVER,

If Q9="CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT", then I want rows A73:A98 to be hidden.

I have stumbled across numerous formulas in my research, but most of it was ridiculously confusing and the ones that were a little more clear didn't work at all or just hid the content of those rows instead of actually hiding the rows altogether? Not really sure what was going on there.

Any help would be greatly appreciated!

Thanks so much!

 

[TWienholz]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If ActiveSheet.Range("Q9") = "CORE SURVEY ONLY – NO CQs" Then
        Rows("35:61").EntireRow.Hidden = True
            Else
                Rows("35:61").EntireRow.Hidden = False
    End If
    
    If ActiveSheet.Range("Q9") = "CUSTOM QUESTIONS FOR QRM REVIEW" Or ActiveSheet.Range("Q9") = "CUSTOM QUESTIONS AUDIT" Then
        Rows("73:98").EntireRow.Hidden = True
            Else
                Rows("73:98").EntireRow.Hidden = False
    End If
    
End Sub

Right click the sheet you want this on and click view code, and paste it in there (do not paste into a normal module)

Use the above code if you want the change to happen automatically

If you want to run it manually with a macro, or a button linked to a macro use below

Sub HideRowss()
    
    If ActiveSheet.Range("Q9") = "CORE SURVEY ONLY – NO CQs" Then
        Rows("35:61").EntireRow.Hidden = True
            Else
                Rows("35:61").EntireRow.Hidden = False
    End If
    
    If ActiveSheet.Range("Q9") = "CUSTOM QUESTIONS FOR QRM REVIEW" Or ActiveSheet.Range("Q9") = "CUSTOM QUESTIONS AUDIT" Then
        Rows("73:98").EntireRow.Hidden = True
            Else
                Rows("73:98").EntireRow.Hidden = False
    End If
    
End Sub

DISCLAIMER: I'm not super familiar with worksheet change, I got both of these working under the listed conditions but if anyone knows if there is a better way let me know, thanks

I also wanted to say thank you to you for your initial code! It worked perfectly with the revision that Joe4 suggested. Thanks so much for taking the time to help!

 

[Twollaston]

I also wanted to say thank you to you for your initial code! It worked perfectly with the revision that Joe4 suggested. Thanks so much for taking the time to help!

No problem, happy to help you buddy

 

[gazzaw]

I have a workbook with multiple work sheets is there a way to do this for the workbook so that what ever sheet I am working on it runs without adding this to every sheet. Just in case I have to add more identical sheets?