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

abarbee314

New Member
Joined
Apr 5, 2013
Messages
16
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)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

montecarlo2079

Board Regular
Joined
Feb 9, 2011
Messages
207
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

Board Regular
Joined
Feb 9, 2011
Messages
207
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

New Member
Joined
Apr 5, 2013
Messages
16
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

Board Regular
Joined
Feb 9, 2011
Messages
207

ADVERTISEMENT

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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Building on the last suggestion

this will unhide row 1 if the opposite is true

Code:
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

New Member
Joined
Apr 5, 2013
Messages
16

ADVERTISEMENT

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

Board Regular
Joined
Feb 9, 2011
Messages
207
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
 
Last edited:

abarbee314

New Member
Joined
Apr 5, 2013
Messages
16
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. :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,768
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top