Excel Macro Hiding Rows with single predefined Condition

[VictorOpokin]

Dear experts, could you please kindly help with the following Excell challenge.
In my spreadsheet I need to have an option to automatically hide all the rows, where formula results in the "Hide" text. Just to highlight, all the cells are with formulas, not text.
What code can do this?

 

[tlowry]

Hope this helps…

This is a VBA solution to your request. If you need a formula, then you should specify that in the request.

Install instructions:

• Open a copy of your Excel application for testing.
• Go into development mode (alt F11)
• Create a Module (Insert | Module)
• Copy and paste the code into the module (usual way)
• Save the file (Ctrl S)
• Exit development mode (Alt F11)
• Have the sheet that is the target for the row “hiding” displayed (active)
• Run the macro hideFormula (Alt F8 | Select | Run)
• Test
• Let me know the results

Sub hideFormula()
    Dim cCell
    On Local Error Resume Next
    For Each cCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Cells
        If cCell.Text = "Hide" Then Rows(cCell.Row).EntireRow.Hidden = True
    Next cCell
End Sub

 

[VictorOpokin]

Thank you! It works perfectly fine. Exactly what was required.
May I use your experience a little bit more and ask for an advice on how to automate unhiding of the rows? And is it possible to modify the code in order to run it from other active spreadsheet for the specific one? I mean, to run the code from an active main sheet for another one called "Summary".
Thank you!

 

[tlowry]

May I use your experience a little bit more and ask for an advice on how to automate unhiding of the rows?

Do you want to "unhide" all the hidden rows or only those hidden by this macro?

to run the code from an active main sheet for another one called "Summary"

Sub hideFormula()
    Dim cCell
    On Local Error Resume Next
    For Each cCell In Sheets("Summary").Cells.SpecialCells(xlCellTypeFormulas).Cells
        If cCell.Text = "Hide" Then Sheets("Summary").Rows(cCell.Row).EntireRow.Hidden = True
    Next cCell
End Sub

 

[VictorOpokin]

Do you want to "unhide" all the hidden rows or only those hidden by this macro?

Actually it would be the best to have an fucntion opening all the rows. Could you please help with it?

 

[tlowry]

OK, here's the deal.

This code will do what you want, except if:

• hideFormula is run and it hides some rows
• An outside process changes one of the cells (that caused a row to be hidden) to something other than "Hide"
• UnhideFormula will not "unhide" that row

If this is a problem, then a more complicated solution is required.

Sub UnhideFormula()
    Dim cCell
    On Local Error Resume Next
    For Each cCell In Sheets("Summary").Cells.SpecialCells(xlCellTypeFormulas).Cells
        If cCell.Text = "Hide" Then Sheets("Summary").Rows(cCell.Row).EntireRow.Hidden = False
    Next cCell
End Sub

 

[VictorOpokin]

Thank you. It works perfectly, when unhiding previosely hidden rows.
But is it possible to have a code unhiding all the rows, independently of text in it?

 

[tlowry]

Sub UnhideAll()
    Sheets("Summary").Rows.Hidden = False
End Sub

 

[VictorOpokin]

Hope this helps…

This is a VBA solution to your request. If you need a formula, then you should specify that in the request.

Install instructions:

• Open a copy of your Excel application for testing.
• Go into development mode (alt F11)
• Create a Module (Insert | Module)
• Copy and paste the code into the module (usual way)
• Save the file (Ctrl S)
• Exit development mode (Alt F11)
• Have the sheet that is the target for the row “hiding” displayed (active)
• Run the macro hideFormula (Alt F8 | Select | Run)
• Test
• Let me know the results

Sub hideFormula()
    Dim cCell
    On Local Error Resume Next
    For Each cCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Cells
        If cCell.Text = "Hide" Then Rows(cCell.Row).EntireRow.Hidden = True
    Next cCell
End Sub

Is it possible to amend the above code with the following functions?
1. I have 2 activeX option buttons. I need the code to hide the required rows (what it is already doing perfectly well) and select the option button called OptionButton1.
2. Go to the Spreadsheet called "Letter".

Thank you in advance!

 

[tlowry]

I think you want to set the OptionButton1 to true rather than select it.

Here’s some code to get you started.

ActiveSheet.OptionButton1.Value = True
ThisWorkbook.Sheets("Letter").Activate