Solved

Else IF Statement that uses text

Posted on 2014-12-23
1
144 Views
Last Modified: 2014-12-23
Hi,

I would need rows "32:37" to collapse if cell "B33" did not equal to "Mail Form to:"
If they did equal "Mail Form to:", then they would expand

Below is the code I am using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngIntersect As Range
Set rngIntersect = Intersect(Target, Range("$A$12:$A$1500"))

If Not rngIntersect Is Nothing Then

    If Range("C24").Value = 0 Then
        Rows("24:29").EntireRow.Hidden = True
    Else
        Rows("24:29").EntireRow.Hidden = False
    End If
    
    If Range("C23").Value = 0 Then
        Rows("23").EntireRow.Hidden = True
    Else
        Rows("23").EntireRow.Hidden = False
    End If

    If Range("C39").Value = 0 And Range("C40").Value = 0 Then
            'both ranges are zero, hide rows 30-35
            Rows("38:47").EntireRow.Hidden = True
        ElseIf Range("C40").Value > 0 Then
            'range > 0, display rows 32-35
            Rows("40:47").EntireRow.Hidden = False
            Rows("38").EntireRow.Hidden = False
            'hide row 31
            Rows("39").EntireRow.Hidden = True
        ElseIf Range("C39").Value > 0 Then
            'range > 0, display row 31
            Rows("38:39").EntireRow.Hidden = False
            'hide rows 32-35
            Rows("40:45").EntireRow.Hidden = True
        Else
            'display rows 30-35
            Rows("38:47").EntireRow.Hidden = False
        End If
        
    If Range("C49").Value = 0 And Range("C50").Value = 0 Then
            Rows("48:57").EntireRow.Hidden = True
        ElseIf Range("C49").Value > 0 Then
            Rows("50:55").EntireRow.Hidden = True
            Rows("48:49").EntireRow.Hidden = False
        ElseIf Range("C50").Value > 0 Then
            Rows("50:57").EntireRow.Hidden = False
            Rows("49").EntireRow.Hidden = True
        Else
            Rows("48:57").EntireRow.Hidden = False
        End If
End If

End Sub

Open in new window

0
Comment
Question by:Boston617
1 Comment
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
So, what is your question??

Rows("32:37").EntireRow.Hidden = (Range("B44").Value <> "Mail Form to:")

Open in new window


btw, it seems that we are talking about row visbility. Collapse and expand means grouping or pivot tables.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now