Hide Unhide columns due to set value

bobbly_bob

New Member
Joined
Mar 26, 2009
Messages
6
We have a sheet used to calculate staff entitlements, unfortunately it now has approximately 30 staff listed and only about one third are current employees.

What I'm planning on doing is using a validated cell with the options of "all", "current", "non-current" which can be selected by the user. Then in row 1 across the page, each staff member will be listed in their column as current or non-current. Depending on which option is selected in the validated cell, I want the appropriate staff members to be hidden / unhidden.

Any help greatly appreciated.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi Bob

Welcome to the Board

Not sure how helpful this is but if that data was transposed you could use the inbuilt autofilter feature of excel. outside of that you'd need some VBA code. How versed are for implementing that?

Regards


Dave
 
Upvote 0
Cheers Dave,

I've mucked around with using the autofilter and it can't quiet do what I'm after. I think this is mainly due to each one of the employees having multiple columns.

I think it will need VB, but except for maybe the very basics I can remember very little of how to write in that form so any help would be great,

Cheers
 
Upvote 0
OK Bob

I'd like to see how your data is laid out. Remember the first rule of excel is to lay data out well then the functions and features will work best. Lets see you can use Colo's HTML Maker

I'll be around to help


Dave
 
Upvote 0
hope this suits, just a print screen,
So any given person has three columns, so first spare is AX, AY, AZ.
I was planning on having something simple like in AX1, AY1 and AZ1 if ax31<>"","non-current","current". And then a1 to have the validated options of viewing "current", "non-current" and "all". So the macro would need to hide the columns that don't apply and display all columns when "all" is selected. employee details begin in column B and extend across in that same format.

Thanks again for the help
template.jpg
 
Upvote 0
Place this code in a new module (Alt+F11, Insert>Module, Paste, then Alt+Q to return to Excel)

Assumptions: Drop-down is in A1
You don't have enough staff to go past column DA

Code:
Sub ShowHide()
    Dim Rng As Range, _
        c As Range
    Dim bHide As Boolean
    Dim lngCol As Long
        
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    'show all columns first and determine last used column
    Range("B1:DA1").EntireColumn.AutoFit
    lngCol = Cells(1, Columns.Count).End(xlToLeft).Column
       
    For Each c In Range(Cells(1, 2), Cells(1, lngCol))
        'hide columns if required
        If Range("A1").Value = "All" Then
            Exit Sub
        ElseIf Range("A1").Value = c.Value Then
            bHide = False
        Else
            bHide = True
        End If
        c.EntireColumn.Hidden = bHide
    Next c
    
    Application.Calculation = xlCalculationAutomatic
End Sub

Denis
 
Upvote 0
Cheers SydneyGeek,

that works, but is there a way to get around uing the AutoFit and keeping current widths? those columns inbetween contain fairly large formulas, so as they are being hidden / unhidden with the autofit the result just looks like a mess.
 
Upvote 0
Yep. You could change the Autofit line to something like

Code:
    Range("B1:DA1").ColumnWidth = 15

Play with the width number until you get the value you need

Denis
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top