Excel - Trigger Hide Row 2

[Rougy]

I need to know how to automatically hide a row when the value of a given cell = 0

Currently I'm trying an if/then statement

=if(a1 = 0, ?.Selection.EntireRow.Hidden = True)

I don't know what goes in the ? part, if anything.

Help would be appreciated.

Rougy

 

[JVFriederick]

Can you use a filter instead?

"FilterCode" as shown below is a range name with a value=0. First column (1) in the filter table has the value of 0 for all records you wish to hide.

Selection.AutoFilter ' Turn filter on with criteria
Selection.AutoFilter Field:=1, Criteria1:=FilterCode

 

[Rougy]

Hi JV,

Thanks for the note. I'm currently using autofilter to manually adjust the columns via a macro.

The problem is, it's not fool-proof. The user can sometimes forget to press the macro button and incomplete data is printed out.

What I'm trying to find is a 'trigger' mechanism that will display a row when the value in a given cell does not equal zero.

Sometime today I'll try to upload the excel file here:

http://www.geocities.com/smpcpa01/

so that you can see what I'm trying to do.

I'll post it sans the macro for security reasons, and will post the macro code in an accompanying text file.

Gracias Hombre,

Rougy

 

[DaleWatson123321]

Rougy,

Another option...

Why don't you consider changing your macro button to read something like "Print Report".

In addition, you could (easily) set up a separate sheet for extraction of ONLY the records specified by your selection criteria.

Then, to GUARANTEE that the user does NOT print the current page, you could (easily) change the print range on the current page to a completely separate range (off to the side) in which you could place a message such as "YOU FORGOT TO USE THE "PRINT REPORT" button - You MUST ALWAYS use this button to print this report". I expect this would be VERY effective in getting his/her attention.

I created a sample file to demonstrate, and would be prepared to email this to you. Just email me, and I'll attach it via return email.

The following is the code, which includes an additonal routine for setting the print range of the extracted data.


Dim FirstCell As String
Dim LastCell As String
Dim LastRow As String
Dim LastColumn As String
Dim Print_Range As String
Dim data As Range

Sub Extract()

Application.ScreenUpdating = False
Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("out&quot, _
Unique:=False
Application.Goto Reference:="out"
Set_PrintRange
Application.ScreenUpdating = True
Application.Goto Reference:="R1C1"

End Sub


Sub Set_PrintRange()

ActiveCell.Offset(1, 0).Select
FirstCell = ActiveCell.Address
Get_Last_Row
LastColumn = "H"
LastCell = LastColumn & LastRow
Print_Range = FirstCell & " : " & LastCell
Range(Print_Range).Name = "PR"
ActiveSheet.PageSetup.PrintArea = "PR"
ActiveSheet.PageSetup.PrintTitleRows = "$1:$4"

End Sub


Sub Get_Last_Row()

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(3, 0).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

End Sub


I would hope you'll give this a try. There can be additional benefits from adopting the process of "extracting" data - such as being able to select the COLUMNS you wish to have included, not just the rows.

Don't hesitate to ask for the file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Rougy]

Mr. Watson,

Thanks so much for the help. I'll take it into account.

I'm afraid that some of it is over my head, but I welcome the challenge.

Is there any way to write an 'automatic' macro that runs in the background?

That's pretty much what I need - some sort of code that would be 'triggered' by a zero in a given cell, causing the cell's row to be hidden until the value is changed to something other than zero.

It's a print issue, but it's also a user issue. I've got people typing over formulas that are already linked to other sheets.

Also, I can't get the Geocities site to download the Excel file. The "ftp.geocities.com..." part doesn't work the way it does on other sites.

Thanks again, Dale.

Rougy

 

[JVFriederick]

See if this works for you ........

Columns("A:A&quot.Select
Selection.EntireRow.Hidden = False ' Unhide all rows
For IntR = 10 To 70 ' Set higher later if needed
ActiveSheet.Rows(IntR).Columns(6).Select ' Column F = 6
If ActiveSheet.Rows(IntR).Columns(6) < 0.01 Then Selection.EntireRow.Hidden = True
Next IntR
Range(&quot;A1&quot.Select

 

[JVFriederick]

Oooops. Didn't check after writing the macro that more postings were made.......

 

[DaleWatson123321]

Rougy,

First, let me suggest that you ALSO consider the advice &quot;JV&quot; offers, as there are ALWAYS other options worth considering, and JV's advice is always GOOD. The main point here, is that TWO (or more) heads are better than one, and I'm sure JV and I BOTH invite each others' participation.


Let me &quot;take a stab&quot; at what you have described in your latest posting ...one at a time.

1) &quot;Also, I can't get the Geocities site to download the Excel file&quot; I'm not sure what you mean by this. If you might happen to (somehow) be referring to the Excel file I offered to email you ? ? ? ...then the proper method of receiving my sample file would be for you to email me - at dwatson@bsi.gov.mb.ca - and I will attach the file to a return email.

2) &quot;causing the cell's row to be hidden until the value is changed to something other than zero.&quot; This sounds to me like a &quot;difficult&quot; situation - mainly because IF the row is hidden, it would be difficult (not possible) for the user to enter a value while the row is hidden. You could potentially user other formulas on another sheet which refer back to the user-input sheet, but then that sort of arrangement can get &quot;messy&quot; and potentially can create MORE problems rather than eliminate problems.

3) &quot;I've got people typing over formulas that are already linked to other sheets.&quot; I would suggest that you consider enabling the &quot;protection mode&quot; for the worksheet. This means: ...first using &quot;Format ...Cells ...Protection&quot;, and 'un-checking' &quot;Locked&quot; for the entire range of cells which you WANT the user to have access to - i.e. the user-input-cells. Then use &quot;Tools ...Protection ...Protect Sheet&quot; which will then protect the entire sheet - except for those cells you have &quot;un-Locked&quot;. Of course, it is IMPORTANT that you write down the PASSWORD you use to protect the sheet. You should ALSO consider sharing the password with a co-worker, in case of an emergency situation.

4) “I'm afraid that some of it is over my head, but I welcome the challenge.” ---and--- “Is there any way to write an 'automatic' macro that runs in the background?” It is possible to have a macro “running in the background”, but, given that “some of this is over your head”, I would suggest that you “start off small”. Rather than “jumping into VB with BOTH feet”, I would suggest you start with using some of the “built-in” functionality of Excel. Some VB experts are capable of writing VB code that can duplicate much of the “built-in” functionality of Excel, but this approach, I believe, is like “re-inventing the wheel”. In any case, being relatively new to VBA, I would NOT be the best person to offer advice on the specifics of “macros running in the background”.

My BEST advice, however, is to start by looking at my example file. As mentioned, just email me, and I’ll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Rougy]

Thank you all for your help.

Ciao, Rougy

 

[Rougy]

ATTENTION.

MAY I HAVE YOUR ATTENTION PLEASE.

DALE WATSON IS GOD.

ROUGY

 

[JVFriederick]

Please don't feed the animals !!