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

abarbee314

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

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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! :)
 
Upvote 0
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:
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top