Hide if 'x' 1

[shytott]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[shytott]

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

 

[SkipVought]

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 [highlight #729FCF]the last column[/highlight] with data in that row. So if you can identify [highlight #729FCF]that column[/highlight] 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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

ActiveSheet.UsedRange.EntireColumn.Hidden = True

this will unhide those columns

ActiveSheet.UsedRange.EntireColumn.Hidden = False

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[shytott]

Hi Skip

Hopefully the attached will show what I'm after. I've saved it with some cols pre hidden, so if you click 'UNHIDE', then the cols will be revealed. I want to be able to click the 'HIDE' button which will hide every col with an 'x' at the top in row 1.

Cheers
Andy

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[shytott]

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

 

[SkipVought]

Good luck to your sports performances.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein