Shortcut to show/hide certain columns?

[slam]

I thought my answer to this was going to be Grouping, a feature I've never used before, but I get the message "This can't be done on a multiple range selection", so I need an alternative solution.

I want columns A, B, C, D to be shown at all times. Then I need functionality to show all columns, or only certain columns (in additional to A, B, C, D). Specifically, it would be each 7th column starting with E. So, for instance, a button to show columns E, L, S, Z, AG, AN, another button to show F, M, T, AA, AH, AO. And so on...

At the moment, my data extends to column AT, but this data will extend by 7 columns per month, so it would be good to set it up to dynamically capture the additional columns from the start.

Is there any built in functionality for this, or is a macro the solution?

Thanks

 

[Micron]

This should hide one of the sets. If they are already hidden, then change 0 to some number, or you can use .Autofit

Sub Button7_Click()
Dim i As Integer, n As Integer
Dim col As Column

n = 5
For i = 0 To 42 Step 7
    Sheets("Sheet4").Columns(n + i).ColumnWidth = 0
Next

End Sub

Not sure why you'd want a button for each set? That means 12 buttons for 12 months of the year? Why not one button and somehow pass the starting column to the code. Perhaps a range inputbox, or cell double click or anything that would provide that. Inputbox could also provide the width if you knew what that one number should be.

 

[slam]

Hi Micron,

Thanks for the reply. My 7 repeating columns are 7 different attributes from my database, and each instance of these 7 attributes are from a different snapshot in time. I need to show only 1 attribute at a time to show how it has changed from one snapshot to the next, and I already have some Conditional Formatting to highlight differences.

My VBA skills are very limited. I added your code as a new module, and updated the sheet reference, but, it's giving me a "Compile error: User-defined type not defined" on the 3rd row, "Dim col As Column"

I'm thinking that once this is working, I create 7 buttons for each of the attributes, then an 8th button to show All, and link those to each block of code?

If it's relevant, this is for Excel 365.

Thanks

 

[Micron]

That message always (?) means you're missing a code library reference. Maybe one of these:
Microsoft ActiveX Data Objects #.# library (mine is 6.1)
Microsoft Office xx Object Library (mine is 16)
Microsoft Visual Basic for Applications Extensibility x.x (mine is 5.3) << I would try this one first.

You cannot choose the first column in a group and have one procedure handle any group? I'd find a way to manage 1 button that does the same thing, not 7 buttons that do the same thing. Change one thing about the process and you need to make changes in 7 places. The "undo" button thing would be OK, but not sure if you also meant 7 of those. That's just nuts.

 

[slam]

Hi Micron,

I'll investigate the code library reference issue, thanks.

Sorry if I'm not explaining the set up that I'm looking for very well. Here's a screenshot of how it looks after I already created those buttons, so you can understand what I'm going for.

 

[Alex Blakenburg]

That "Dim col as Column" is invalid and not used in @Micron's code, just remove it.

 

[slam]

Hi @Micron & @Alex Blakenburg code runs without that line in it, thanks.

I've tried it with my first button so far, but it's doing the opposite of what I require. Instead of showing column A, B, C D, then all the Phase columns, it's hiding all the Phase columns.

Any help would be greatly appreciated!

 

[Micron]

The requirement is still a bit vague to me. You show cols A to D as being visible, so why mention them? It's better to clearly state exactly what the results were. I can't tell if those cols are hidden to begin with.

I mentioned in my post that you change the 0 to a number or use Autofit to show the columns, or zero to hide them. Did you do that?

I forgot to remove the Dim col As Column line after I decided I didn't need it. However, I tested and that code didn't raise an error, so I still think your error was due to a missing library reference.

 

[slam]

Hi @Micron, apologies if I have still not explained it well. I'll try again in the context of some of the buttons I have added that you can see in my screenshot above. I never want Columns A to D to be hidden.

• Clicking the Phase button should make only these columns visible: A to D, and E, L, S, Z, AG, AN (or in other words, every 7th column starting with E)
• Clicking the Start Date button should make only these columns visible: A to D, and F, M, T, AA, AH, AO (or in other words, every 7th column starting with F)
• ... and so on for all 7 buttons
• The All button shows all columns again, and I have this working already.

Thank you!

 

[Micron]

What I'm saying is that if they are never hidden, there is no need to code for that. Or are they sometimes hidden? That's what's not clear to me. You forgot to answer this?

I mentioned in my post that you change the 0 to a number or use Autofit to show the columns, or zero to hide them. Did you do that?

You could also use this approach:

For i = 0 To 42 Step 7
    Sheets("Sheet4").Columns(n + i).Hidden = False 'false to show, True to hide
Next