Hide/Unhide Toggle

Von Pookie

MrExcel MVP

Joined

Feb 17, 2002

Messages

13,686

• Jan 16, 2008

• #11

Because of this:

Code:

    If ccell.EntireRow.Hidden = True Then cell.EntireRow.Hidden = False

When the If statement is on one line like that you do not need an End If. So, the 'Else' statement is essentially stranded outside of any If statement.

Edit: Also, your use of the For/Next is a bit messed up there, too. Try:

Code:

For Each cell In Range("A8:A207")
    If cell.EntireRow.Hidden = True Then
        cell.EntireRow.Hidden = False
    Else
        If cell.Value = "xxxxx" Then
            cell.EntireRow.Hidden = True
        End If
    End If
Next cell

 

Last edited: Jan 16, 2008

Upvote 0

stir-crazy

New Member

Joined

Dec 28, 2005

Messages

29

• Jan 16, 2008

• #12

THANK YOU THANK YOU THANK YOU THANK YOU!


I was trying so many different permutations of the code, got as close as getting everything to work unless I actually changed the value from "xxxxx". Yours, however, works flawlessly!

Thanks again!

So, so long as I break the if/then statements up with a line break (after then), I can insert the Else statements? Cool!

 

Upvote 0

B

Boller

Banned

Joined

Apr 11, 2006

Messages

2,328

• Jan 16, 2008

• #13

"Every trick I know, I learned on this board."

The code mortgageman posted is in the first chapter (Lesson 1) of a Microsoft book I read when first learning VBA (Step By Step Ms Excel 97 VB).

So there are other sources for learning "tricks".

 

Upvote 0

Von Pookie

MrExcel MVP

Joined

Feb 17, 2002

Messages

13,686

• Jan 17, 2008

• #14

stir-crazy said:

So, so long as I break the if/then statements up with a line break (after then), I can insert the Else statements? Cool!

Click to expand...

Well, it can get even more confusing than that. You can even do If Else all on one line

Code:

For Each cell In Range("A8:A207")
    If cell.EntireRow.Hidden = True Then cell.EntireRow.Hidden = False Else If cell.Value = "xxxxx" Then cell.EntireRow.Hidden = True
Next cell

 

Upvote 0

mortgageman

Well-known Member

Joined

Jun 30, 2005

Messages

2,015

• Jan 18, 2008

• #15

Boller said:

"Every trick I know, I learned on this board."

The code mortgageman posted is in the first chapter (Lesson 1) of a Microsoft book I read when first learning VBA (Step By Step Ms Excel 97 VB).

So there are other sources for learning "tricks".

Click to expand...

I never said there were not. I was just pointing out a great feature of this board.

 

Upvote 0

B

Boller

Banned

Joined

Apr 11, 2006

Messages

2,328

• Jan 18, 2008

• #16

"I never said there were not. I was just pointing out a great feature of this board."

Just thought it strange that you have not learnt any "tricks" from anywhere other than this board.<!-- / message --><!-- sig -->

 

Upvote 0

mortgageman

Well-known Member

Joined

Jun 30, 2005

Messages

2,015

• Jan 19, 2008

• #17

Boller said:

"I never said there were not. I was just pointing out a great feature of this board."

Just thought it strange that you have not learnt any "tricks" from anywhere other than this board.<!-- / message --><!-- sig -->

Click to expand...

I suspect that this discussion is not only OT but better for the lounge, but anyway:

It has been decades since I worked in any sort of software coding. I moved over into finance and then sales of mortgages. I use Excel for my business, and the only book I read was Walkenback's book. I have found that this board is a far better resource for someone like me - self taught - than anywhere else I can find. For those who have had a more "formal education" (whatever that is) in Excel, this board may not be as valuable - though I suspect that even that is not true.

At the end of day, it would seem that how is less important than the fact itself - except for the requirement to give credit where it is due.

 

Last edited: Jan 19, 2008

Upvote 0

B

Boller

Banned

Joined

Apr 11, 2006

Messages

2,328

• Jan 19, 2008

• #18

.

 

Upvote 0