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!

 

[Twollaston]

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

 

[Joe4]

Worksheet_Change event procedures run when cells are on a sheet are updated manually.
If the value in Q9 is a formula referencing another sheet, if the value changes (via the formula), this will NOT trigger the Worksheet_Change event procedure to run on that page (as nothing was manually updated on that page).

The cell Q9 in the current worksheet is determined by data from 'ProjectDataSheet'!J43

How is J43 being updated on the ProjectDataSheet? If that is being updated manually, then you could put Worksheet_Change code on THAT page that hides the data on the other page.
Or, you could use Worksheet_Calculate code on the page with Q9, which runs everytime any calculation on that sheet is updated. The only caveat that there is that the code may run a lot more than necessary (won't hurt the output, but could slow the performance).

 

[Twollaston]

If the value in Q9 is a formula referencing another sheet, if the value changes (via the formula), this will NOT trigger the Worksheet_Change event procedure to run on that page (as nothing was manually updated on that page).

This is a good point that i wasn't aware of. Thank you for mentioning

 

[Joe4]

This is a good point that i wasn't aware of. Thank you for mentioning

You are welcome. One of the cool things about "Worksheet_Change", is that you can trigger it to run only when a certain range is updated. The "Target" argument that is automatically passed in to the procedure is the range that was manually updated that just triggered the procedure to run. So you can check that range and have the rest of the code only process if it meets your requirements.

For example, if you wanted to see if just one cell was updated, and it was in row 4, you could have code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 And Target.Row = 4 Then
        MsgBox "An update was made in row 4"
    End If
End Sub

Unfortunately, Worksheet_Calculate does not have a Target argument, so you cannot tell which value changed on the sheet, just that "some" value changed. So you really cannot hone in on a specific range being updated with Worksheet_Calculate.

 

[Twollaston]

Yeah I've been slowly realizing how powerful the worksheet changes are for automation. Thank you for the explanation, never really understood what was happening in them for the most part, but now that makes sense.

 

[TWienholz]

Worksheet_Change event procedures run when cells are on a sheet are updated manually.
If the value in Q9 is a formula referencing another sheet, if the value changes (via the formula), this will NOT trigger the Worksheet_Change event procedure to run on that page (as nothing was manually updated on that page).


How is J43 being updated on the ProjectDataSheet? If that is being updated manually, then you could put Worksheet_Change code on THAT page that hides the data on the other page.
Or, you could use Worksheet_Calculate code on the page with Q9, which runs everytime any calculation on that sheet is updated. The only caveat that there is that the code may run a lot more than necessary (won't hurt the output, but could slow the performance).

This explains why Twollaston's code for the the current page is not working.

As you mentioned the ProjectDataSheet cell J43 is a manual entry. So if I used Twollaston's code on the ProjectDataSheet instead I could set the Rows to hide on the current sheet as Sheet5.Rows(35:61)? Is that the proper syntax for that?

 

[Joe4]

Glad I could help.
They really are a useful tool!

 

[TWienholz]

Worksheet_Change event procedures run when cells are on a sheet are updated manually.
If the value in Q9 is a formula referencing another sheet, if the value changes (via the formula), this will NOT trigger the Worksheet_Change event procedure to run on that page (as nothing was manually updated on that page).


How is J43 being updated on the ProjectDataSheet? If that is being updated manually, then you could put Worksheet_Change code on THAT page that hides the data on the other page.
Or, you could use Worksheet_Calculate code on the page with Q9, which runs everytime any calculation on that sheet is updated. The only caveat that there is that the code may run a lot more than necessary (won't hurt the output, but could slow the performance).

Based on your comment I updated the code to read as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveSheet.Range("j43") = "" Then
Sheet5.Rows("35:61").EntireRow.Hidden = True
Else
Sheet5.Rows("35:61").EntireRow.Hidden = False
End If

If ActiveSheet.Range("j43") > 0 Then
Sheet5.Rows("73:98").EntireRow.Hidden = True
Else
Sheet5.Rows("73:98").EntireRow.Hidden = False
End If

End Sub

It works perfectly!!! Thank you so much for your help!!

 

[Twollaston]

Yeah just paste the code on the sheet you have and change Q9 to J43
and I use Worksheets("Sheet5").Rows(35:61) but i think both actually work depending on what the sheet name is in vba

I think Sheet5.Rows would be using the code name of the sheet meaning that when you go into vba the sheets will be named like this:
Sheet1(Sheet1)
Sheet2(Income)
Sheet5(Sheet5)

so the one on the left i think is the code name and on the right is what you have named the sheet on the front end