• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically Hide Line Based on Value of Cell

L

loach

New Member
On the attached workbook, there is a VB code which, in column A, returns a Hyperlink to each sheet in the workbook. It is essentially a sheet index.
What I would like is: If the date in column AE on the Open Contracts sheet has a value of 1/1/1990 or earlier, then the line is automatically hidden. If the date is 1/2/1990 or later, then the line is automatically unhidden.
The date in column AE is comes from the project tabs (28-P02, 08-P02) via the INDIRECT function.
Is this possible? Or do I need to clarify my question?
Thanks in advance for any and all help.
 

Attachments

I looked at your file. My solution is kinda bulky but it works. Rather than hide rows I grabbed an index formula I use. It only returns a value based on a singular condition. So I copied your open contracts page and inserted it based on a lookup of the one you had. From here I would just do a vlookup on all the remaining information.

This would allow you one sheet without anything filtered out, and one with the desired information filtered. You can also set the filter based on a single cell value. In this case you could change the date you wish to filter.
 

Attachments

Thank you Nebu! It works beautifully. I do have one question: If the date I want to reference is in column W rather than AE, do I simply change the AE in the code to W, or is there something else which must change too?

Thank you too Scott. Your approach is one I had not considered. Many paths to the top of the same mountain, eh?
 
I have tried to add two more criteria for hiding a line (if the value in column H is Yes, and the value in I is later than 12/31/2014), but it only seems to recognize the criteria in F. If all three criteria are true, it should hide the line. What have I done wrong?


Code: 
Sub HideRow()
Application.ScreenUpdating = False
Dim i, j As Long
i = Sheet54.Cells(Rows.Count, "A").End(xlUp).Row
For j = 2 To i
  If Sheet54.Range("F" & j).Value <= 100000 Or Sheet54.Range("F" & j) = "" And Sheet54.Range("H" & j).Value = "yes" Or Sheet54.Range("H" & j) = "" And Sheet54.Range("I" & j).Value > "12/31/2014" Or Sheet54.Range("I" & j) = "" Then
  Rows(j).EntireRow.Hidden = True
  End If
Next
Application.ScreenUpdating = True
End Sub
Sub UnHideRow()
Application.ScreenUpdating = False
Sheet54.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub
 
Hi:

Can you post some sample data, without a sample it is difficult to understand what is going wrong with the macro. My first assumption is that it is something to do with data types.
Thanks
 
Your original macro works beautifully. I adapted it to another report (hence my question above about data being in "AE" rather than "W") and it worked beautifuilly there too.

I am now trying to adapt it again to a scenario where I have three variables which determine if a line is hidden or not. The code above is my (I am sure clumsy) attempt to adapt the code.

I will post a sheet when I get a spare moment, but what I am trying to accomplish can be summed up in an "if-then" statement:
If F1 is less than 100,000 OR H1 is "yes" OR I1 is later than 12/31/2014, then hide line 1
 
I have attached a spreadsheet. The "Quarterly Report" tab is where this needs to work.
The criteria for hiding the line is: If amount is under $100,000, OR If award date is before 4/1/15 OR Board approval is "Yes" THEN hide line.

Thanks again for all your help with this.
 

Attachments

Hi:

Please find the code.
Code: 
Sub HideRow()
Application.ScreenUpdating = False
Dim i, c As Long
i = Sheet55.Cells(Rows.Count, "A").End(xlUp).Row

For c = 6 To i
    With Sheet55
        If .Range("E" & c) < 100000 Or .Range("F" & c) < 4 / 1 / 15 Or .Range("G" & c) = Yes Then
            Rows(c).EntireRow.Hidden = True
        End If
    End With
Next
Application.ScreenUpdating = True
End Sub

Thanks
 
Thanks Nebu. It almost works, but the code only seems to recognize the amount in column E.

If the amount is under $100,000 it hides the line perfectly, but the date in column F being before or after 4/1/2015, and the yes/no in column G seem to have no effect.

Currently, it hides the line if the value of column E is under 100,000 and column G has no data (because I am using indirect references, no data in G populates the cell with a 0 (zero)).
 
Try this !​
Code: 
Sub Demo()
    With Sheet55
        .[X1:Z1].Value = .[E1:G1].Value
        .[X2:Z2].Value = Array(">=100000", ">=" & #4/1/2015#, "No")
        .Cells(1).CurrentRegion.AdvancedFilter xlFilterInPlace, .[X1:Z2]
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Last edited:
Thanks Marc. I tried your code above, and it ignored all criteia and hid every line on the sheet so I guess it worked too well ;c)
 
I understand the problem now, and it is 100% my fault. I did not specify well enough that the tab I want this to work on is the quartely report tab.

From your post above it looks like you were solving for the Open Contracts tab.
 

Nope, as you should check before in your VB editor,
codename Sheet55 is "Quaterly Report" worksheet !

Just do some training with manual advanced filter directly from tab,
it's well explained in Excel help and in tutorials over the Web …
Start with only one criteria, just date for example and once succeed,
try with two or more, not difficult, beginner can achieve …
​
 
Interesting... Marc, when I plug your formula into the sheet I attached in #9 above, it works like a champ. When I plug it into the "real" workbook, it misbehaves.
I am sure the problem is between the screen and the chair (i.e.: me). I will see if I can play with it and figure out what I am doing wrong.

One last thing... do you have an "unhide" formula for your code above?

Thanks again for all your help.
 
As suspected, the problem was with me. When I compared the "real" workbook to the sample I posted, I noticed a slightly different configuration. Once I corrected this, the code worked perfectly.

I should be able to adapt this to use with tons of other stuff. Thanks!
 
You must log in or register to reply here.
Back
Top