Problem with hiding rows when cell is chaged

I

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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Can you lay out an example for us where it is not working properly?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Thanks - I'll give that a try.

PS - Does that mean you don't see anything obviously wrong with the code
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
347
Peter_SSs
Peter_SSs
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
919
dmt32
D
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
374
kevin9999
K
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
378
kevin9999
K
D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
334
NoSparks
N

Forum statistics

Threads
1,214,515
Messages
6,119,974
Members
448,934
Latest member
audette89

Share this page

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