Solved

# Else IF Statement that uses text

Posted on 2014-12-23
146 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
``````
0
Question by:Boston617
1 Comment

LVL 33

Accepted Solution

ste5an earned 500 total points
ID: 40515073
So, what is your question??

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

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

## Featured Post

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

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

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