range names in macros - syntax again
 

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

range names in macros - syntax again
 

On Sep 20, 9:03 pm, "Dean" wrote:
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

range names in macros - syntax again
 

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?

"JW" wrote in message
ps.com...
On Sep 20, 9:03 pm, "Dean" wrote:
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

range names in macros - syntax again
 

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

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


--


Gary


"Dean" wrote in message
...
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?

"JW" wrote in message
ps.com...
On Sep 20, 9:03 pm, "Dean" wrote:
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

range names in macros - syntax again
 

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

range names in macros - syntax again
 

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

Dean

"Rick Rothstein (MVP - VB)" wrote in
message ...
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