×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hide if 'x'

Hide if 'x'

Hide if 'x'

(OP)
Hi
I’m wanting to create a button that will hide any col which has an ‘X’ placed in it in row 1. This will be in consecutive cols, there will be no gaps in the row of ‘X’s. and will be in operation from D1 to GR1. I’m thinking the code will be along the lines of ‘Hide col if D1:GR1=’x’ but as you can see, Im struggling! Any help greatly appreciated.

Many Thanks

RE: Hide if 'x'

Hi,

What about going the opposite direction of un-hiding columns?

Have you considered that?

Just wondering the reason for hiding columns and then how do you get things back to normal???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Hide if 'x'

(OP)
Hi Skip
Thanks for your reply.
This spreadsheet covers data which is being added week by week in columns (each week covering 5 cols) so even with my index cols A-D frozen, it gets a bit unwieldy moving around, so I was just wanting to make it easier to hide ‘old’ data. I will later on in the year, be wanting to re-examine the data week by week so to be able to quickly control it by some sort of ‘hide if = ‘x’ ’ would be very useful.

Andy

RE: Hide if 'x'

Ok, I see what you're attempting to accomplish.

So why put some extraneous value (x) in a column? I'd assume that each week-by-week column has a similar value identifying the week in some row. You then might want all columns from column A thru the last column with data in that row. So if you can identify that column in that row, then that would identify the columns to hide without having to add any 'x" in row 1, a needless extraneous value.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Hide if 'x'

Actually, this simple statement will hide all columns that contain data...

CODE

ActiveSheet.UsedRange.EntireColumn.Hidden = True 

this will unhide those columns

CODE

ActiveSheet.UsedRange.EntireColumn.Hidden = False 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Hide if 'x'

CODE

Sub HideX()
'hide columns from D1 to last x in row 1
    With ActiveSheet
        Range(.Cells(1, "D"), .Cells(1, .Cells.Columns.Count).End(xlToLeft)).EntireColumn.Hidden = True
    End With
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Hide if 'x'

BUT...

here's the problem with having your data nice and pretty like you have recorded the weekly results.

How will you calculate who's got the most Pts or the greatest Dif?

I used FAQ68-5287: NORMALIZE Your Table using the PivotTable Wizard to make the My DATA table in the uploaded workbook that also has two queries displaying the teams with the most Pts and highest Diff.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Hide if 'x'

(OP)
Hi Skip
Absolutely spot on, thanks so much for the code, its doing exactly what I’m after.

The data recorded each week is cumulative (ie the 3rd set of data are the results after 3 weeks, and not the results that happened on week 3), so would not be back added onto previous weeks. I left the left hand column in alphabetical order to make it easier to record successive weeks results. My next step is to form a graph plotting each team performance over the duration of a year.

Thanks Again
Andy

RE: Hide if 'x'

Good luck to your sports performances.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close