Hiding rows based on cell contents

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,077
I need to get a formula or a piece of code that looks at cell D3 and if the number is not 5 then rows 800 & 810 will be hidden.

Can anybody help with this?

Many Thanks,

Andrew
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Right click the name tab at the bottom of the worksheet that you want to have this functionality and select "View Code" from the popup menu that appears, then copy/paste the following code into the code window that opened up...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("D5")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("D5") <> 5
  End If
End Sub
 
Upvote 0
When I change the data in my key cell I get an error:
Compile Error:
Ambiguous name detected: Worksheet_Change
 
Upvote 0
Hi Rick,

I think there is a typo in your post.

Instead of Range("D5") the OP says:
"..a piece of code that looks at cell D3 and if the number is not 5.."

So i think the correct is

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("[B]D3[/B]")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("[B]D3[/B]") <> 5
  End If
End Sub

M.
 
Upvote 0
That error message means you have to Change event procedures in the sheet module (the code window that opened when you followed my procedure). There can only be one Change event procedure in a sheet module. If the other Change event is your first attempt to do what my code does, delete it. If there is other code in that procedure doing other things, then you will need to post its code so we can integrate the code from both procedures into a single event procedure for you.

EDIT NOTE: Andrew caught a typo (thanks Andrew) that I made in my code. It code you should use is this...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("D3")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("D3") <> 5
  End If
End Sub
 
Last edited:
Upvote 0
I also have this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "M3" Then Me.Shapes("Shape51").Visible = Target.Value >= 5
End Sub
 
Upvote 0
Try replacing both of your current Change event procedures with this single one...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("D3")) Is Nothing Then
    Range("800:800,810:810").EntireRow.Hidden = Range("D3") <> 5
  End If
  If Not Intersect(Target, Range("M3")) Is Nothing Then
    Me.Shapes("Shape51").Visible = Target.Value >= 5
  End If
End Sub
 
Upvote 0
rows 800 & 810 don't seem to hide when a number other than 5 is entered...
Any ideas?
 
Upvote 0
It does for me. You did install the code in a worksheet module, not a standard module, right? Standard modules are where macros go, but that is not where evernt code goes. In case some of this is new to you, the easiest way to get to the correct worksheet module is to right click the name tab at the bottom of the worksheet that you want to have this functionality, then click "View Code" on the popup menu that appears. This will open the worksheet module's code window where you are to copy/paste the code at. Speaking of code, I have a minor tweak for the code I posted earlier; use this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("D3")) Is Nothing Then
Range("800:800,810:810").EntireRow.Hidden = Range("D3") <> 5
End If
If Not Intersect(Target, Range("M3")) Is Nothing Then
Me.Shapes("Shape51").Visible = Range("M3") >= 5
End If
End Sub
 
Upvote 0
Awesome! thanks for all of your help!

By the way, would you happen to know how to have a macro button open up a gmail and compose a message?
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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