Solved

code stops working near (but not at) end

Posted on 2014-12-30
17
63 Views
Last Modified: 2014-12-31
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

0
Comment
Question by:Boston617
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
17 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40524463
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40524508
Better yet would be to attach your workbook if it doesn't contain sensitive information.
0
 

Author Comment

by:Boston617
ID: 40524529
Hi Martin,

How would I attach my workbook?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:Martin Liss
ID: 40524536
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
 

Author Comment

by:Boston617
ID: 40524546
Below is my workbook, I put a $1 value on a few states to show how it should appear
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40524574
Given the sheet with the data you posted, what is it doing wrong?
0
 

Author Comment

by:Boston617
ID: 40524582
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40524596
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
 

Author Comment

by:Boston617
ID: 40524613
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40524630
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
 

Author Comment

by:Boston617
ID: 40524635
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40524646
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
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40524663
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
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40524669
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
 

Author Comment

by:Boston617
ID: 40525385
You're right! darn typos
Thank you!
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40525595
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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question