Hide a row based upon value in Column 1 (need script)

[abarbee314]

Okay, this is my first post AND I am not super-familiar with VBA/scripts. I know how to insert codes and that's about it. I work as a legal trainer, and usually am the one showing people how to do stuff. So, if you want to educate me within your response, that's better for me in the long run. If you want to just give me the code to copy/paste, that's fine, too.

This should be easy for you Excel gurus...
I have an attorney I work with who wants to hide a row based upon a cell value.

Background:
- The cell value appears to be a manually-inserted one, not a formula
- we are on Win 7 / office 2010

Sheet info:
His first example was... if A1 > $h$123, then hide row 1.
H123 is the lookup cell, and obviously is not moving.
I believe there is a range involved, waiting on confirmation. It is something like (B7:B106), again, still compared to H123.

Thanks, in advance, for your help!

Aaron

No was was ever fired for smiling too much (Me)

 

[montecarlo2079]

This should work but I have a question. how do you want this to happen? when a cell is selected? do you want a button? does it need to execute when the value of h123 changes?

This is a simple if statement. basically it says if the value of A1 is greater than the value of H123, then hide the row. otherwise dont do anything.

The way it is set up now, it would need to be attached to a button or manually run.



Sub Macro1()

If Range("a1").Value > Range("h123").Value Then
Rows("1:1").Select
Selection.EntireRow.Hidden = True
End If
End Sub

 

[montecarlo2079]

taking it a step further

place this code on the sheet, and not in a module. if the value of h123 changes, then the code will execute, but it will only hide row 1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("h123")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Range("a1").Value > Range("h123").Value Then
Rows("1:1").Select
Selection.EntireRow.Hidden = True
End If


End If
End Sub

 

[abarbee314]

I think he likes this first suggestion (since the second one only hides row 1). He asked about using a button as a trigger and I sent him the Microsoft instruction to use the Developer tab to add one. But, I don't think he'll want to go through all that. Can we trigger the macro to run when the data changes? or is the "manual" start a better option?

Thanks!

 

[montecarlo2079]

well the question is, this macro is only set up to hide row 1. so if the data in h123 changes again, or if a1 is not greater than h123, nothing will happen

do you need it to hide each visible row so if b1 is now greater than H123 then hide row 2?

 

[Dryver14]

Building on the last suggestion

this will unhide row 1 if the opposite is true

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim KeyCells As Range
 Set KeyCells = Range("h123")
 If Not Application.Intersect(KeyCells, Range(Target.Address)) _
 Is Nothing Then
 If Range("a1").Value > Range("h123").Value Then
 Rows("1:1").EntireRow.Hidden = True
 Else
 Rows("1:1").EntireRow.Hidden = False
 End If
 End If
 End Sub

 

[abarbee314]

well the question is, this macro is only set up to hide row 1. so if the data in h123 changes again, or if a1 is not greater than h123, nothing will happen

do you need it to hide each visible row so if b1 is now greater than H123 then hide row 2?

YES! But, I just clarified the range with him... it is B7 through B106. All rows would be invidually compared to H123. So, "is B7 greater than H123? yes. Hide Row 7". And so on... through B106.

Additionally, he didn't want to go all thru with the button stuff. He said he's totally cool with the Run Manually process.

Thank you SO much!

 

[montecarlo2079]

how about this?

for this to work, the value in h123 needs to change, or the cell needs to be touched etc.

This goes in the worksheet module.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("h123")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
For Each c In Range("b7:b106")
If c.Value > Range("h123") Then c.EntireRow.Hidden = True
Next
End If
End Sub



If you want the same results but want to control it manually with a button.

here you go.

Sub Macro1()
'
'
For Each c In Range("b7:b106")
If c.Value > Range("h123") Then c.EntireRow.Hidden = True
Next

End Sub

 

[abarbee314]

We used the first one (in the worksheet module) and it worked perfectly!!! Thank you so much!

Have a super weekend and give yourself a high-five from me.