Re: If / Else Code (hiding rows based on cell value)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Please forgive my ignorance, but where you have

...Rows(x).Hidden = True
...Rows(y).Hidden = True

what goes in front of the ellipses? As written it gives a compile
error/syntax error, so I’m guessing you meant for me to plug something in
there. Also, could you briefly explain to me what this code is really saying
in laymen’s terms? I “sort of” get it, but then again I’m not really sure I
totally understand the x’s and y’s.

Dumb blonde trying to code,
jj

"Don Guillett" wrote:

If I am to assume that you only want certain rows hidden then perhaps you
should unhide all and then just hide the desired rows with this instead

Sub Hideifj1()
With Sheets("Approved Rate Review (2)")
.Rows.Hidden = False
Select Case UCase(.Range("j1"))
Case Is = "RETAIL", "MOTO": x = "17": y = "17"
Case Is = "SMALL TICKET": x = "17:19": y = "17"
Case Is = "INTERCHANGE PLUS", "APPROVED RATE REVIEW": x = "17:22": y =
"27:32"
Case Is = "DIAL PAY": x = "17:18": y = "26:33"
Case Else
MsgBox "None to Hide"
End Select
..Rows(x).Hidden = True
..Rows(y).Hidden = True
End With

--
Don Guillett
SalesAid Software
dguillett1@xxxxxxxxxxxxx
"jjones" <jjones@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7264C794-A154-443E-896F-7A1D09D2D6D2@xxxxxxxxxxxxxxxx
I'm still a newbie at writing macros, but here's the situation. I've
successfully mapped data from an Access database over to a form letter in
Excel. The verbiage in the letter varies depending on the industry type
selected (options are: Retail, Small Ticket, MOTO, Interchange Plus, Dial
Pay). This is stored in cell J1. Since some paragraphs are shorter than
others, I sometimes end up with extra blank rows between paragraphs
depending
on which option has been chosen. As part of my Auto_Open macro, I want
these
extra rows of blank space hidden depending on the value in J1. As I have
the
code written now, however, the next if statement overrides the previous
one,
making all but the last one irrelevant really. I've tried changing it a
couple of different ways but I just end up with compile errors. Anybody
know
how to make this work? I'm open to other ideas if this can't be
accomplished
with If statements. Here's the code I have now:


If Range("J1").Value = "Retail" Then
Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True
Else
Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False
End If
If Range("J1").Value = "Small Ticket" Then
Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden =
True
Else
Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden =
False
End If
If Range("J1").Value = "MOTO" Then
Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True
Else
Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False
End If
If Range("J1").Value = "Interchange Plus" Then
Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden =
True
Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden =
True
Else
Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden =
False
Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden =
False
End If
If Range("J1").Value = "Dial Pay" Then
Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden =
True
Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden =
True
Else
Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden =
False
Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden =
False
End If




.

Relevant Pages

  • Re: If / Else Code (hiding rows based on cell value)
    ... "Don Guillett" wrote: ... The verbiage in the letter varies depending on the industry ... extra rows of blank space hidden depending on the value in J1. ... code written now, however, the next if statement overrides the previous ...
    (microsoft.public.excel.programming)
  • Re: If / Else Code (hiding rows based on cell value)
    ... The verbiage in the letter varies depending on the industry type ... Since some paragraphs are shorter than ... extra rows of blank space hidden depending on the value in J1. ... code written now, however, the next if statement overrides the previous ...
    (microsoft.public.excel.programming)
  • Re: Links2 in graphics mode in X?
    ... the linking process with runtime shared libraries, ... so you need to go get libpng.a and libpng.so (depending ... de-modernizing C++ codes, or de-ansi99ing C codes. ... impossible to compile without linking against something, ...
    (comp.os.linux.misc)
  • Re: what is the problem about my makefile
    ... Depending on the system, you *can*, depending on just what one ... I think the statement "You can't compile a .o file." ... it is not a cake, but merely a bunch of ingredients mixed together in a ... Only when the baking is finished, ...
    (comp.lang.c)
  • Re: Python 2.4.4 vs. 2.3.6
    ... I will be using gcc 2.3.2, 3.3and 3.4.3to cross ... compile it depending on the platform. ...
    (comp.lang.python)