How do I automatically hide "FALSE" rows?

[Kiley]

I have created the following "IF" formula =IF('officer
data'!Z4="fse",'officer data'!A4) to pull employees that have a status of
"fse" from another worksheet in the same workbook. I only want to show the
rows that are "TRUE" and automatically hide rows that are "FALSE"

 

[Luke M]

You could apply a filter (Data, Filter, AutoFilter) and then have your filter
show everything that does not equal FALSE. (under custom)

 

[Kiley]

That works but I was hoping that there was a formula to help do this because
I am using this workbook as a template and the employees may change. If I
appy the filter and change the employees, the new employees are not
automatically visible.

 

[Dave Peterson]

There's no formula that will hide rows.

You'll either need a macro or teach the users to show the data and filter it
again.

(If you teach them, they'll be able to use filters in lots of workbooks!)

 

[Kiley]

I did create a macro but it will not work if I protect the worksheet. I need
to protect the worksheet so that the formulas cannot be changed. Is there a
way around this?

 

[Dave Peterson]

Your macro could unprotect the worksheet, do its work and reprotect the
worksheet.

Or you could protect the worksheet in code using the "userinterfaceonly" parm.

Saved from a previous post.

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

 

[Kiley]

I don't know anything about code. So, I didn't understand the code part of
your response. I did add the "unprotect" and "protect" to my macro and that
worked. However, once I closed the workbook and came back in and tried the
macro, it prompted me to unprotect the worksheet. Did I miss something?

 

[Dave Peterson]

It sounds like it.

I'm not sure how you run your macro, but it should look something like:

Sub YourMacro()

activesheet.unprotect password:="hithere"

'your code to do the work

activesheet.protect password:="hithere"

End sub

Depending on where/how you run the code, it could vary. But you'll have to
share more.

I don't know anything about code. So, I didn't understand the code part of
your response. I did add the "unprotect" and "protect" to my macro and that
worked. However, once I closed the workbook and came back in and tried the
macro, it prompted me to unprotect the worksheet. Did I miss something?

 

[Kiley]

this is what the code looks like after I set up the macro by creating the
steps involved.


ActiveSheet.Unprotect
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>FALSE", Operator:=xlAnd, _
Criteria2:="<>#VALUE!"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Dave Peterson]

And it works ok for you or you have trouble?

this is what the code looks like after I set up the macro by creating the
steps involved.

ActiveSheet.Unprotect
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>FALSE", Operator:=xlAnd, _
Criteria2:="<>#VALUE!"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Kiley]

I am having problems when I close the workbook and come back in, it will ask
for a password before I can perform the macro. The worksheet is protected
and I set up the macro to unprotect the sheet do the filter and then proctect
the sheet again. This worksheet is a template and I want someone to be able
to go into the sheet and perform the macro without having to unprotect it
first.

 

[Dave Peterson]

You need to specify the password on both the .protect and unprotect lines:

ActiveSheet.Unprotect password:="hithere"
...
ActiveSheet.protect password:="hithere"

I am having problems when I close the workbook and come back in, it will ask
for a password before I can perform the macro. The worksheet is protected
and I set up the macro to unprotect the sheet do the filter and then proctect
the sheet again. This worksheet is a template and I want someone to be able
to go into the sheet and perform the macro without having to unprotect it
first.

 

[Kiley]

I added the password and not it says I have an error in the last line?

ActiveSheet.Unprotect Password:="tarcrune"
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>FALSE", Operator:=xlAnd, _
Criteria2:="<>#VALUE!"
ActiveSheet.Protect Password:="tarcrune"
DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Luke M]

change last two lines to:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="tarcrune"

Note that this needs to be one continuous line in VBA (don't have part of it
on a second line)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I added the password and not it says I have an error in the last line?

ActiveSheet.Unprotect Password:="tarcrune"
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>FALSE", Operator:=xlAnd, _
Criteria2:="<>#VALUE!"
ActiveSheet.Protect Password:="tarcrune"
DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Kiley]

That worked! Thank you Luke and Dave! Much appreciated!