Is it possible in Excel to have the result of a formula hide rows.

[NeedHelp]

I was wondering if it was possible to have the result of a formula hide or
unhide rows.
Also, is it possible to use a checkbox in excel where if the box is checked
off then a row is unhidden and if the box remains unchecked a row is hidden?

 

[FSt1]

hi
formulas return values, they cannot perform actions like hide and unhide rows.
but you can hide and unhide rows(and columns) with a check box. drop a check
box on the sheet from the tool box. right click the check box and click view
code.
paste this code in the code window.
Private Sub checkbox1_Click()
If Rows("10:13").Hidden = True Then
Rows("10:13").Hidden = False
checkbox1.BackColor = RGB(0, 0, 255)'change the color too
checkbox1.Caption = "hiden" 'set the captions too
Else
If Rows("10:13").Hidden = False Then
Rows("10:13").Hidden = True
checkbox1.BackColor = RGB(245, 30, 5)
checkbox1.Caption = "unhiden"
End If
End If
End Sub

set the rows to your liking. edit the check box name to what ever you set it
too.

regards
FSt1

 

[Dave Peterson]

#1. Nope. Formulas return values to the cells that hold the formulas. They
can't do things like hide/unhide rows--or assign values to other cells.

#2. Yep.

You could put a checkbox from the Forms toolbar on your worksheet and assign it
a macro like:

Option Explicit
Sub testme()
With ActiveSheet
If .CheckBoxes(Application.Caller).Value = xlOn Then
.Range("17:23").EntireRow.Hidden = True
Else
.Range("17:23").EntireRow.Hidden = False
End If
End With
End Sub

or just a little easier to type:

Option Explicit
Sub testme()
With ActiveSheet
.Range("17:23").EntireRow.Hidden _
= CBool(.CheckBoxes(Application.Caller).Value = xlOn)
End With
End Sub

I'm not sure what "checked off" means <vbg>. You may need to use xloff instead
of xlon.

Other things you could do...
Data|Filter|autofilter if there's a value in a field that can be used.

An event macro (worksheet_Calculate) that would monitor worksheet calculations
and if the criteria were met, something would happen.