Hide Unhide columns with macro but with changing columns

[tigger6333]

Greetings.

I have a sheet with 7 buttons, each button attached to a macro that wil show/hide the relevant columns for the button.

The sheet has Row 1 with Headings but not in consecutive cells. Sometimes the cells are blank until the next heading appears.
So it may be A1=Heading1, B1-F1 Blank, G1=Heading2, H1-S1 Blank, T1-Heading3, etc.

Row 2 has subheadings for each section, no blank cells.

Each button relates to each Heading. So clicking the Button 1 will show/hide the columns from A to F, Button 2 G-S, and so on.

The macro I have attached to the buttons is as follows:

Sub ShowHide_Heading1()
If ActiveSheet.Columns("A:F").Hidden = True Then ActiveSheet.Columns("A:F").Hidden = False Else ActiveSheet.Columns("A:F").Hidden = True
End Sub

with each button having it's own macro with the column letters adjusted accordingly.

My question is this:

If I add a column in the sheet, the macros have to be manually adjusted.
How can I code in any changes so the code takes care of any column changes?

I was thinking of something like looking up the column of the desired heading (in the above example this would give the A parameter), and then looking up the column of the next heading - 1 ( this would give the F parameter). However my VBA skills are of the cut and paste variety, and I haven't worked out how to achieve my aim.

Is there anyone with the skill who can help?

Thanks in advance.
the Tigg

 

[jasonb75]

Maybe something on the lines on

Sub ShowHide_Heading1()
startCol = WorksheetFunction.Match("Heading 1", [1:1], 0)
endCol = WorksheetFunction.Match("Heading 2", [1:1], 0) - 1
For a = startCol To endCol
ActiveSheet.Columns(a).Hidden = (ActiveSheet.Columns(a).Hidden = False)
Next
End Sub

With the headings changed to match yours, however this would not work for the last button (unless there is another heading after the last set of columns to hide).

For the last button use

Sub ShowHide_HeadingX()
startCol = WorksheetFunction.Match("Heading 2", [1:1], 0)
endCol = ActiveSheet.UsedRange.Columns.Count
For a = startCol To endCol
ActiveSheet.Columns(a).Hidden = (ActiveSheet.Columns(a).Hidden = False)
Next
End Sub

It's not foolproof, if the headings are changed then it would fail. I'm sure that it can be done by counting the non- blank cells in the first row, but it's not working for me so far, maybe something simple I'm overlooking.

 

[tigger6333]

jasonb75,

thank you soooo much. It works a treat. You have helped me to learn a bit more about vb too!

I guess the slightly "staccato" disappearing/reappearing of columns is due to the loop?

I appreciate the time you've taken to reply.
Thanks again.

A very grateful Tigger6333

 

[tryagain]

Select ur first part of columns in row 1 and name it part1
Select ur second part of columns in row 1 and name it part2
and so on

assign these codes to ur buttons

Sub Hide1()
Range("part1").EntireColumn.Hidden = Not Range("part1").EntireColumn.Hidden
End Sub
Sub Hide2()
Range("part2").EntireColumn.Hidden = Not Range("part2").EntireColumn.Hidden
End Sub
Sub Hide3()
Range("part3").EntireColumn.Hidden = Not Range("part3").EntireColumn.Hidden
End Sub

 

[jasonb75]

This code only works, but only if your button names are named consecutively, starting from 1.
For example if you right click the button to hide columns A:E then "?????? 1" appears in the name box above A1, with "??????" being the shape type used for the button, the last character needs to be 1, if it's not you can click in the name box and change it. The next button name should end 2, etc. The code supports a maximum of 9 buttons without change.

The first line of the code finds that number from the button used to start the macro, then uses that to determine the position of the header in row 1, this means 1 macro for all 7 buttons instead of 1 for each.

Sub ShowHide_Heading()
Application.ScreenUpdating = False
Header = Right(ActiveSheet.Shapes(Application.Caller).Name, 1)
endCol = ActiveSheet.UsedRange.Columns.Count
For Each myCell In [1:1]
sc = sc + (Len(myCell) > 0)
ec = ec + (Len(myCell) > 0)
If Abs(sc) > Header Or myCell.Column > endCol Then Exit For
If Abs(sc) = Header Then Columns(myCell.Column).Hidden = (ActiveSheet.Columns(myCell.Column).Hidden = False)
Next
Application.ScreenUpdating = true
End Sub

Also stopped the screen flicker while macro runs.

Hope this helps

 

[jasonb75]

Just realised there was a missing line, the variable header needs to be declared or the code fails.

Dim header as Long

needs to be added as first line of the code.

 

[tigger6333]

jasonb75,

Once again....awesome! Above and beyond what I expected.

Thanks very much.
I am taking some time to peruse your code and nut out how it works so
one day maybe I can help someone too!

Cheers
A very grateful
Tigger6333