Problem with hiding rows when cell is chaged

Innoguide

Board Regular
Joined
Sep 13, 2011
Messages
159
I Have the following code in my "Input" worksheet and every time cell D7 changes the sheet should unhide rows 11 through 26, and then depending if cell H for the row is blank, as a result of the change to D7, it should hide that row and all the rows below it. I have similar code in another worksheet that works perfectly, but here for some reason, the code is behaving erratically (sometimes it will hide rows, sometimes not).

Any suggestions would be appreciated.

Here is the code that is in the worksheet

VBA Code:
Option Explicit
Private Sub worksheet_change(ByVal target As Range)
Dim MyTarget As Range
Set MyTarget = Range("d7")
If Not Intersect(target, MyTarget) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect
Rows("11:26").EntireRow.Hidden = False
       
If [h11] = "" Then
Range("f11:f26").ClearContents
Rows("11:26").EntireRow.Hidden = True
           
ElseIf [h12] = "" Then
Range("f12:f26").ClearContents
Rows("12:26").EntireRow.Hidden = True
       
ElseIf [h13] = "" Then
Range("f13:f26").ClearContents
Rows("13:26").EntireRow.Hidden = True
               
ElseIf [h14] = "" Then
Range("f14:f26").ClearContents
Rows("14:26").EntireRow.Hidden = True
           
ElseIf [h15] = "" Then
Range("f15:f26").ClearContents
Rows("15:26").EntireRow.Hidden = True
                               
ElseIf [h16] = "" Then
Range("f16:f26").ClearContents
Rows("16:26").EntireRow.Hidden = True
                               
ElseIf [h17] = "" Then
Range("f17:f26").ClearContents
Rows("17:26").EntireRow.Hidden = True
       
ElseIf [h18] = "" Then
Range("f18:f26").ClearContents
Rows("18:26").EntireRow.Hidden = True
       
ElseIf [h19] = "" Then
Range("f19:f26").ClearContents
Rows("19:26").EntireRow.Hidden = True
       
ElseIf [h20] = "" Then
Range("f20:f26").ClearContents
Rows("20:26").EntireRow.Hidden = True
       
ElseIf [h21] = "" Then
Range("f21:f26").ClearContents
Rows("21:26").EntireRow.Hidden = True
       
ElseIf [h22] = "" Then
Range("f22:f26").ClearContents
Rows("22:26").EntireRow.Hidden = True
       
ElseIf [h23] = "" Then
Range("f23:f26").ClearContents
Rows("23:26").EntireRow.Hidden = True
           
ElseIf [h24] = "" Then
Range("f24:f26").ClearContents
Rows("24:26").EntireRow.Hidden = True
           
ElseIf [h25] = "" Then
Range("f25:f26").ClearContents
Rows("25:26").EntireRow.Hidden = True
       
ElseIf [h26] = "" Then
Range("f26:f26").ClearContents
Rows("26:26").EntireRow.Hidden = True
           
End If
Application.EnableEvents = False
End If
ActiveSheet.Protect
End Sub
 

Some videos you may like

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Can you lay out an example for us where it is not working properly?
 

Innoguide

Board Regular
Joined
Sep 13, 2011
Messages
159
Sure Joe4, it is not hiding and unhiding rows nor clearing the contents of column F most of the time - but I can't find a pattern
If I unhide all the rows manually, sometimes it will hide them after I change D7, but then if I do the same thing again, it won't

In fact. if I just leave the following it does not work to unhide the rows

Option Explicit

Private Sub worksheet_change(ByVal target As Range)

Dim MyTarget As Range
Set MyTarget = Range("D7")

If Not Intersect(target, MyTarget) Is Nothing Then
Application.EnableEvents = False

ActiveSheet.Unprotect
Rows("11:26").EntireRow.Hidden = False


Application.EnableEvents = True
End If
ActiveSheet.Protect
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I was hoping for a specific example, so I could try to recreate the exact problem here on my end. But without having your data sheet, I don't know how good that would be.
So, I recommend using the following debugging technique.
Put a breakpoint on the first IF lime in your code.
Then, make a change to D7, and it will take you into your code.
Then, hit F8 to advance the code one line at a time.
Do that over and over, and watch which lines of code it is hitting.
Then you might be able to see exactly where things are going off track for you.
 

Innoguide

Board Regular
Joined
Sep 13, 2011
Messages
159
Thanks - I'll give that a try.

PS - Does that mean you don't see anything obviously wrong with the code
 

Innoguide

Board Regular
Joined
Sep 13, 2011
Messages
159
I put the core code in a separate macro and triggered it manually and it worked fine. So I ended up reworking the code that triggers when D7 is changed.

Here's what finally worked. Gl;ad it's working but still not sure why the previous version was not working:unsure:

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 
If Not Intersect(Target, Range("d7")) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect
        Rows("11:26").EntireRow.Hidden = False
        Range("f11:f26").ClearContents
        If [h11] = "" Then
        Range("f11:f26").ClearContents
        Rows("11:26").EntireRow.Hidden = True
            
        ElseIf [h12] = "" Then
        Range("f12:f26").ClearContents
        Rows("12:26").EntireRow.Hidden = True
        
        ElseIf [h13] = "" Then
        Range("f13:f26").ClearContents
        Rows("13:26").EntireRow.Hidden = True
                
        ElseIf [h14] = "" Then
        Range("f14:f26").ClearContents
        Rows("14:26").EntireRow.Hidden = True
            
        ElseIf [h15] = "" Then
        Range("f15:f26").ClearContents
        Rows("15:26").EntireRow.Hidden = True
                                
        ElseIf [h16] = "" Then
        Range("f16:f26").ClearContents
        Rows("16:26").EntireRow.Hidden = True
                                
        ElseIf [h17] = "" Then
        Range("f17:f26").ClearContents
        Rows("17:26").EntireRow.Hidden = True
        
        ElseIf [h18] = "" Then
        Range("f18:f26").ClearContents
        Rows("18:26").EntireRow.Hidden = True
        
        ElseIf [h19] = "" Then
        Range("f19:f26").ClearContents
        Rows("19:26").EntireRow.Hidden = True
        
        ElseIf [h20] = "" Then
        Range("f20:f26").ClearContents
        Rows("20:26").EntireRow.Hidden = True
        
        ElseIf [h21] = "" Then
        Range("f21:f26").ClearContents
        Rows("21:26").EntireRow.Hidden = True
        
        ElseIf [h22] = "" Then
        Range("f22:f26").ClearContents
        Rows("22:26").EntireRow.Hidden = True
        
        ElseIf [h23] = "" Then
        Range("f23:f26").ClearContents
        Rows("23:26").EntireRow.Hidden = True
            
        ElseIf [h24] = "" Then
        Range("f24:f26").ClearContents
        Rows("24:26").EntireRow.Hidden = True
            
        ElseIf [h25] = "" Then
        Range("f25:f26").ClearContents
        Rows("25:26").EntireRow.Hidden = True
        
        ElseIf [h26] = "" Then
        Range("f26:f26").ClearContents
        Rows("26:26").EntireRow.Hidden = True


            
    End If
Application.EnableEvents = True
ActiveSheet.Protect

End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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