code stops working near (but not at) end

Hi,

The code I have was working fine until I noticed that the entries for 'wv return and 'ut return weren't working but the code still continues to work well after it. Am I overlooking something?

Below is the code I'm using, the ones I'm having trouble with are near the very bottom
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngIntersect As Range
Set rngIntersect = Intersect(Target, Range("$I$12:$I$1500"))

If Not rngIntersect Is Nothing Then

    If Range("B24").Value = 0 Then
        Rows("24:30").EntireRow.Hidden = True
    Else
        Rows("24:30").EntireRow.Hidden = False
    End If
    
    If Range("B23").Value = 0 Then
        Rows("23").EntireRow.Hidden = True
    Else
        Rows("23").EntireRow.Hidden = False
    End If

    If Range("B41").Value = 0 And Range("B42").Value = 0 Then
            'both ranges are zero, hide rows 30-35
            Rows("40:50").EntireRow.Hidden = True
        ElseIf Range("B42").Value > 0 Then
            'range > 0, display rows 32-35
            Rows("42:50").EntireRow.Hidden = False
            Rows("40").EntireRow.Hidden = False
            'hide row 31
            Rows("41").EntireRow.Hidden = True
        ElseIf Range("B41").Value > 0 Then
            'range > 0, display row 31
            Rows("40:41").EntireRow.Hidden = False
            'hide rows 32-35
            Rows("42:48").EntireRow.Hidden = True
        Else
            'display rows 30-35
            Rows("40:50").EntireRow.Hidden = False
        End If
        
    If Range("B52").Value = 0 And Range("B53").Value = 0 Then
            'ny return
            Rows("51:61").EntireRow.Hidden = True
        ElseIf Range("B52").Value > 0 Then
            Rows("53:59").EntireRow.Hidden = True
            Rows("51:52").EntireRow.Hidden = False
            Rows("60:61").EntireRow.Hidden = False
        ElseIf Range("B53").Value > 0 Then
            Rows("53:61").EntireRow.Hidden = False
            Rows("51").EntireRow.Hidden = False
            Rows("52").EntireRow.Hidden = True
        Else
            Rows("51:61").EntireRow.Hidden = False
        End If
        
    If Range("B63").Value = 0 And Range("B64").Value = 0 Then
            'ca return
            Rows("62:71").EntireRow.Hidden = True
        ElseIf Range("B63").Value > 0 Then
            Rows("64:69").EntireRow.Hidden = True
            Rows("62:63").EntireRow.Hidden = False
            Rows("70:71").EntireRow.Hidden = False
        ElseIf Range("B64").Value > 0 Then
            Rows("64:71").EntireRow.Hidden = False
            Rows("62").EntireRow.Hidden = False
            Rows("63").EntireRow.Hidden = True
        Else
            Rows("62:71").EntireRow.Hidden = False
        End If
        
    If Range("B73").Value = 0 And Range("B74").Value = 0 Then
            'ri return
            Rows("72:81").EntireRow.Hidden = True
        ElseIf Range("B73").Value > 0 Then
            Rows("74:79").EntireRow.Hidden = True
            Rows("72:73").EntireRow.Hidden = False
            Rows("80:81").EntireRow.Hidden = False
        ElseIf Range("B74").Value > 0 Then
            Rows("74:81").EntireRow.Hidden = False
            Rows("72").EntireRow.Hidden = False
            Rows("73").EntireRow.Hidden = True
        Else
            Rows("72:81").EntireRow.Hidden = False
        End If
    
    If Range("B83").Value = 0 And Range("B84").Value = 0 Then
            'nj return
            Rows("82:91").EntireRow.Hidden = True
        ElseIf Range("B83").Value > 0 Then
            Rows("84:89").EntireRow.Hidden = True
            Rows("82:83").EntireRow.Hidden = False
            Rows("90:92").EntireRow.Hidden = False
        ElseIf Range("B84").Value > 0 Then
            Rows("84:91").EntireRow.Hidden = False
            Rows("82").EntireRow.Hidden = False
            Rows("83").EntireRow.Hidden = True
        Else
            Rows("82:91").EntireRow.Hidden = False
        End If
        
    If Range("B93").Value = 0 And Range("B94").Value = 0 Then
            'oh return
            Rows("92:101").EntireRow.Hidden = True
        ElseIf Range("B93").Value > 0 Then
            Rows("94:99").EntireRow.Hidden = True
            Rows("92:93").EntireRow.Hidden = False
            Rows("100:101").EntireRow.Hidden = False
        ElseIf Range("B94").Value > 0 Then
            Rows("94:101").EntireRow.Hidden = False
            Rows("92").EntireRow.Hidden = False
            Rows("93").EntireRow.Hidden = True
        Else
            Rows("92:101").EntireRow.Hidden = False
        End If
    
    If Range("B103").Value = 0 And Range("B104").Value = 0 Then
            'ct return
            Rows("102:111").EntireRow.Hidden = True
        ElseIf Range("B103").Value > 0 Then
            Rows("104:109").EntireRow.Hidden = True
            Rows("102:103").EntireRow.Hidden = False
            Rows("110:111").EntireRow.Hidden = False
        ElseIf Range("B104").Value > 0 Then
            Rows("104:111").EntireRow.Hidden = False
            Rows("102").EntireRow.Hidden = False
            Rows("103").EntireRow.Hidden = True
        Else
            Rows("102:111").EntireRow.Hidden = False
        End If
        
    If Range("B113").Value = 0 And Range("B114").Value = 0 Then
            'nc return
            Rows("112:121").EntireRow.Hidden = True
        ElseIf Range("B113").Value > 0 Then
            Rows("114:119").EntireRow.Hidden = True
            Rows("112:113").EntireRow.Hidden = False
            Rows("120:121").EntireRow.Hidden = False
        ElseIf Range("B114").Value > 0 Then
            Rows("114:121").EntireRow.Hidden = False
            Rows("112").EntireRow.Hidden = False
            Rows("113").EntireRow.Hidden = True
        Else
            Rows("112:121").EntireRow.Hidden = False
        End If
    
    If Range("B123").Value = 0 And Range("B124").Value = 0 Then
            'va return
            Rows("122:131").EntireRow.Hidden = True
        ElseIf Range("B123").Value > 0 Then
            Rows("124:129").EntireRow.Hidden = True
            Rows("122:123").EntireRow.Hidden = False
            Rows("130:131").EntireRow.Hidden = False
        ElseIf Range("B124").Value > 0 Then
            Rows("124:131").EntireRow.Hidden = False
            Rows("122").EntireRow.Hidden = False
            Rows("123").EntireRow.Hidden = True
        Else
            Rows("122:131").EntireRow.Hidden = False
        End If
        
    If Range("B133").Value = 0 And Range("B134").Value = 0 Then
            'pa return
            Rows("132:141").EntireRow.Hidden = True
        ElseIf Range("B133").Value > 0 Then
            Rows("134:139").EntireRow.Hidden = True
            Rows("132:133").EntireRow.Hidden = False
            Rows("140:141").EntireRow.Hidden = False
        ElseIf Range("B134").Value > 0 Then
            Rows("134:141").EntireRow.Hidden = False
            Rows("132").EntireRow.Hidden = False
            Rows("133").EntireRow.Hidden = True
        Else
            Rows("132:141").EntireRow.Hidden = False
        End If
        
    If Range("B143").Value = 0 And Range("B144").Value = 0 Then
            'dc return
            Rows("142:151").EntireRow.Hidden = True
        ElseIf Range("B143").Value > 0 Then
            Rows("144:149").EntireRow.Hidden = True
            Rows("142:143").EntireRow.Hidden = False
            Rows("150:151").EntireRow.Hidden = False
        ElseIf Range("B144").Value > 0 Then
            Rows("144:151").EntireRow.Hidden = False
            Rows("142").EntireRow.Hidden = False
            Rows("143").EntireRow.Hidden = True
        Else
            Rows("142:151").EntireRow.Hidden = False
        End If
        
    If Range("B153").Value = 0 And Range("B154").Value = 0 Then
            'ga return
            Rows("152:161").EntireRow.Hidden = True
        ElseIf Range("B153").Value > 0 Then
            Rows("154:159").EntireRow.Hidden = True
            Rows("152:153").EntireRow.Hidden = False
            Rows("160:161").EntireRow.Hidden = False
        ElseIf Range("B154").Value > 0 Then
            Rows("154:161").EntireRow.Hidden = False
            Rows("152").EntireRow.Hidden = False
            Rows("153").EntireRow.Hidden = True
        Else
            Range("152:161").EntireRow.Hidden = False
        End If
        
    If Range("B163").Value = 0 And Range("B164").Value = 0 Then
            'nh return
            Rows("162:171").EntireRow.Hidden = True
        ElseIf Range("B163").Value > 0 Then
            Rows("164:169").EntireRow.Hidden = True
            Rows("162:163").EntireRow.Hidden = False
            Rows("170:171").EntireRow.Hidden = False
        ElseIf Range("B164").Value > 0 Then
            Rows("164:171").EntireRow.Hidden = False
            Rows("152").EntireRow.Hidden = False
            Rows("163").EntireRow.Hidden = True
        Else
            Rows("162:171").EntireRow.Hidden = False
        End If
        
    If Range("B173").Value = 0 And Range("B174").Value = 0 Then
            'vt return
            Rows("172:181").EntireRow.Hidden = True
        ElseIf Range("B173").Value > 0 Then
            Rows("174:179").EntireRow.Hidden = True
            Rows("172:173").EntireRow.Hidden = False
            Rows("180:181").EntireRow.Hidden = False
        ElseIf Range("B174").Value > 0 Then
            Rows("174:181").EntireRow.Hidden = False
            Rows("172").EntireRow.Hidden = False
            Rows("173").EntireRow.Hidden = True
        Else
            Rows("172:181").EntireRow.Hidden = False
        End If
        
    If Range("B183").Value = 0 And Range("B184").Value = 0 Then
            'md return
            Rows("182:191").EntireRow.Hidden = True
        ElseIf Range("B183").Value > 0 Then
            Rows("184:191").EntireRow.Hidden = True
            Rows("182:183").EntireRow.Hidden = False
            Rows("190:191").EntireRow.Hidden = False
        ElseIf Range("B184").Value > 0 Then
            Rows("184:191").EntireRow.Hidden = False
            Rows("182").EntireRow.Hidden = False
            Rows("183").EntireRow.Hidden = True
        Else
            Rows("182:191").EntireRow.Hidden = False
        End If
        
    If Range("B193").Value = 0 And Range("B194").Value = 0 Then
            'il return
            Rows("192:201").EntireRow.Hidden = True
        ElseIf Range("B193").Value > 0 Then
            Rows("194:199").EntireRow.Hidden = True
            Rows("192:193").EntireRow.Hidden = False
            Rows("200:201").EntireRow.Hidden = False
        ElseIf Range("B194").Value > 0 Then
            Rows("194:201").EntireRow.Hidden = False
            Rows("192").EntireRow.Hidden = False
            Rows("193").EntireRow.Hidden = True
        Else
            Rows("192:201").EntireRow.Hidden = False
        End If
        
    If Range("B203").Value = 0 And Range("B204").Value = 0 Then
            'wv return
            Rows("202:211").EntireRow.Hidden = True
        ElseIf Range("B203").Value > 0 Then
            Rows("204:209").EntireRow.Hidden = True
            Rows("202:203").EntireRow.Hidden = False
            Rows("210:211").EntireRow.Hidden = False
        ElseIf Range("B204").Value > 0 Then
            Rows("204:211").EntireRow.Hidden = False
            Rows("202").EntireRow.Hidden = False
            Rows("203").EntireRow.Hidden = True
        Else
            Rows("202:211").EntireRow.Hidden = False
        End If
        
    If Range("B213").Value = 0 And Range("B214").Value = 0 Then
            'ut return
            Rows("212:221").EntireRow.Hidden = True
        ElseIf Range("B213").Value > 0 Then
            Rows("214:219").EntireRow.Hidden = True
            Rows("212:213").EntireRow.Hidden = False
            Rows("200:221").EntireRow.Hidden = False
        ElseIf Range("B214").Value > 0 Then
            Rows("214:221").EntireRow.Hidden = False
            Rows("212").EntireRow.Hidden = False
            Rows("213").EntireRow.Hidden = True
        Else
            Rows("212:221").EntireRow.Hidden = False
        End If
        
    If Range("B223").Value = 0 And Range("B224").Value = 0 Then
            'mi return
            Rows("222:231").EntireRow.Hidden = True
        ElseIf Range("B223").Value > 0 Then
            Rows("224:229").EntireRow.Hidden = True
            Rows("222:223").EntireRow.Hidden = False
            Rows("230:231").EntireRow.Hidden = False
        ElseIf Range("B224").Value > 0 Then
            Rows("224:231").EntireRow.Hidden = False
            Rows("222").EntireRow.Hidden = False
            Rows("223").EntireRow.Hidden = True
        Else
            Rows("222:231").EntireRow.Hidden = False
        End If
        
    If Range("B233").Value = 0 And Range("B234").Value = 0 Then
            'me return
            Rows("232:241").EntireRow.Hidden = True
        ElseIf Range("B233").Value > 0 Then
            Rows("234:239").EntireRow.Hidden = True
            Rows("232:233").EntireRow.Hidden = False
            Rows("240:241").EntireRow.Hidden = False
        ElseIf Range("B234").Value > 0 Then
            Rows("234:241").EntireRow.Hidden = False
            Rows("232").EntireRow.Hidden = False
            Rows("233").EntireRow.Hidden = True
        Else
            Rows("232:241").EntireRow.Hidden = False
        End If
    
End If

End Sub

Open in new window

Boston617Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Oh wait! I see the problem. The code for ut has a typo which I've bolded. I believe it should be 210 and not 200.

    If Range("B213").Value = 0 And Range("B214").Value = 0 Then
            'ut return
            Rows("212:221").EntireRow.Hidden = True
        ElseIf Range("B213").Value > 0 Then
            Rows("214:219").EntireRow.Hidden = True
            Rows("212:213").EntireRow.Hidden = False
            Rows("200:221").EntireRow.Hidden = False
        ElseIf Range("B214").Value > 0 Then
            Rows("214:221").EntireRow.Hidden = False
            Rows("212").EntireRow.Hidden = False
            Rows("213").EntireRow.Hidden = True
        Else
            Rows("212:221").EntireRow.Hidden = False
        End If
0
 
Martin LissOlder than dirtCommented:
Put a breakpoint on line 263 (click in the left-had margin) and run the macro. What are the values of B203 and B204 at that point.
0
 
Martin LissOlder than dirtCommented:
Better yet would be to attach your workbook if it doesn't contain sensitive information.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Boston617Author Commented:
Hi Martin,

How would I attach my workbook?
0
 
Martin LissOlder than dirtCommented:
When you create a post you'll see this. Click it, then click the 'Choose File' button, followed by the green 'Upload File' button.click it
0
 
Boston617Author Commented:
Below is my workbook, I put a $1 value on a few states to show how it should appear
0
 
Boston617Author Commented:
0
 
Martin LissOlder than dirtCommented:
Given the sheet with the data you posted, what is it doing wrong?
0
 
Boston617Author Commented:
the entries for 'wv return and 'ut return don't function at all
the entries for the other states all work fine

basically, if there is a refund due, then only the refund amount line will show
if there is an amount due, then lines "Amount Due" down to "Mail Check..." will show
0
 
Martin LissOlder than dirtCommented:
For the "wv" processing If I unhide all the rows between 50 and 211 and then select a cell in column "I", the code finds that Range("B203").Value = 1 And Range("B204").Value = 0 so the If Range("B203").Value = 0 And Range("B204").Value = 0 Then is false but the ElseIf Range("B203").Value > 0 is true so Rows("204:209") are hidden and Rows("202:203" and Rows("210:211") are unhidden. What would you like to do differently?
0
 
Boston617Author Commented:
That is exactly what I want to happen but Rows("204:209") are not hidden, they still show up when they should not

The code doesn't work even if both B203 and B204 = 0, the rows don't collapse as they should
0
 
Martin LissOlder than dirtCommented:
It does hide them. Do what I did and first manually unhide a range of rows that include those rows and then run the code. Here's what I see when I do that.
They're hidden
0
 
Boston617Author Commented:
I can't seem to be able to get it to do that with me

I also really want to do anything manual (clicking on rows, not inputing data) when I work on this
0
 
Martin LissOlder than dirtCommented:
When you say "I also really want to do anything manual (clicking on rows, not inputing data) when I work on this" did you mean "I also don't really want to do anything manual (clicking on rows, not inputing data) when I work on this". If so I only unhid the rows so I could see the result. If that's not what you mean then please try to explain again.
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
It looks like you have a second typo.

    If Range("B163").Value = 0 And Range("B164").Value = 0 Then
            'nh return
            Rows("162:171").EntireRow.Hidden = True
        ElseIf Range("B163").Value > 0 Then
            Rows("164:169").EntireRow.Hidden = True
            Rows("162:163").EntireRow.Hidden = False
            Rows("170:171").EntireRow.Hidden = False
        ElseIf Range("B164").Value > 0 Then
            Rows("164:171").EntireRow.Hidden = False
            Rows("152").EntireRow.Hidden = False
            Rows("163").EntireRow.Hidden = True
        Else
            Rows("162:171").EntireRow.Hidden = False
        End If
0
 
Boston617Author Commented:
You're right! darn typos
Thank you!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.