range names in macros - syntax again

[Dean]

I have some macro code that hides a certain range of rows (220 to 228).
However, occasionally, a few rows get added in the middle, which messes up
the macro. I think I know that, if I name the range (I did as
"net.profit.rows.on.ProjectCF" though the macro keeps changing some of the
letters to upper case), and use the range name in the macro, it will adjust
for such changes automatically. The first row of code works fine. The
second one, my feeble attempt at the aforementioned fix, doesn't. Please
tell me how to get this range name to be hidden, in the Else condition. If
I need to dimension something too, let me know, please. Thx. Dean

If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True
Else Rows("200:228").Hidden = True

If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else
net.profit.Rows.On.ProjectCF.Hidden = True

 

[JW]

I have some macro code that hides a certain range of rows (220 to 228).
However, occasionally, a few rows get added in the middle, which messes up
the macro. I think I know that, if I name the range (I did as
"net.profit.rows.on.ProjectCF" though the macro keeps changing some of the
letters to upper case), and use the range name in the macro, it will adjust
for such changes automatically. The first row of code works fine. The
second one, my feeble attempt at the aforementioned fix, doesn't. Please
tell me how to get this range name to be hidden, in the Else condition. If
I need to dimension something too, let me know, please. Thx. Dean

If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True
Else Rows("200:228").Hidden = True

If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else
net.profit.Rows.On.ProjectCF.Hidden = True

If ProfitMode = "Net Profit %" Then
Rows("160:208").Hidden = True
Else: Range("net_profit").EntireRow.Hidden = True
End If

 

[Dean]

Thank you very much! I'm not quite sure why my original one worked (I
think) without the End if. Is it only sometimes necessary?

 

[Gary Keramidas]

it would probably more clear to you if you used the basic if, else, end if
structure:

Sub test()
If a = b Then
'do something
ElseIf a = c Then
'do something else
Else
'do something different
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

I'm not quite sure why my original one worked (I think) without

the End if. Is it only sometimes necessary?

There are two acceptable formats for If-Then statements.

If <logical expression> Then <Code>

and

If <logical expression> Then
<Code>
End If

The first form does not require an End If statement, but all your code has
to directly follow the Then keyword. Usually, this form is used when there
is a single code statement to be executed when the logical expression is
true. However, you could place multiple statements following the Then
keyword as long as they are separated from each other by a colon (but doing
so is not recommended as it makes your code much, much harder to read). The
second form is used when there are one or more code statements to be
executed when the logical expression is True. You also have to use the
second form when there will be ElseIf and/or Else tests.

Rick

 

[Dean]

Thanks much for that info. Also, thanks to Gary.

Dean