Hiding rows based on cell contents

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,937
Office Version
2010
Platform
Windows
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
 

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
When I change the data in my key cell I get an error:
Compile Error:
Ambiguous name detected: Worksheet_Change
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,937
Office Version
2010
Platform
Windows
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:

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,937
Office Version
2010
Platform
Windows
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
 

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
rows 800 & 810 don't seem to hide when a number other than 5 is entered...
Any ideas?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,937
Office Version
2010
Platform
Windows
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
 

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,233
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top