Solved

code stops working near (but not at) end

Posted on 2014-12-30
17
55 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
  • 10
  • 7
17 Comments
 
LVL 46

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 46

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 46

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
 

Author Comment

by:Boston617
ID: 40524550
0
 
LVL 46

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 46

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 46

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 46

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 46

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 46

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 46

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

831 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