On my spreadsheet, I record annual leave, sick absence, training away-days etc for a whole department. I would like to be able to show only the columns with for example an ‘AL’ for annual leave on some given row, so I can easily see which dates are holiday for that person (row). This should work just like filtering rows on some value in one column. Excel won’t do this by itself, and copying, transposing and filtering is too clumsy. Any help TIA
Frank
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Hide / unhide range of columns with one click
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Hide / unhide range of columns with one click
- This topic has 13 replies, 5 voices, and was last updated 14 years, 4 months ago.
AuthorTopicWSfrankwalsh1962
AskWoody LoungerNovember 24, 2010 at 10:51 am #473247Viewing 12 reply threadsAuthorReplies-
Tim Sullivan
Member -
RetiredGeek
AskWoody_MVPNovember 24, 2010 at 3:05 pm #1256607Frank,
How about this?
Edit: This could be modified to request the value to filter on so you could select SL, HL, Etc.
-
WSfrankwalsh1962
AskWoody LoungerNovember 25, 2010 at 10:29 am #1256672Hello Retiredgeek, this almost works – the trouble is where the range of columns is being counted. It works perfectly for Moe, as he is on holiday on day 10. For other staff, the rows up to the last AL are hidden if blank, but rows after that seem not to be selected. So for example days 3, 5, 7, 8, 9,10 remain un-hidden for Manny (with day 7 being his last AL day).
My spreadsheet has a set of columns for name, room number, extension, work team etc etc., then columns are labeled for every day of the year, cells being completed for annual leave, sick absence, study days, trips out of office and a dozen more, with a 2-letter code for each, ie AL. SK, SD and so on.
I’m looking to choose one such code, maybe SK for sick absence, then just hide all the other ‘day of the year’ columns, so I can see all the dates when that person is sick and record them for HR to have.
The column range will be the same in every case, Jan 1 is column AA and Dec 31 is (I think) OA or OB in a leap year.
This kind of thing might be really useful for a lot of people, I can think of several other applications in my job.
Many thanks again. Frank
-
WSColinBurrows
AskWoody LoungerNovember 25, 2010 at 11:46 am #1256677This isn’t a complete solution, but a technique that may be useful.
Put a formula in, say, row 1 that returns an error if that particular column should be hidden. If you do that, then you just need a couple of lines of code, I think:
Cells.EntireColumn.Hidden = False
Rows(“1:1”).SpecialCells(xlCellTypeFormulas, xlErrors).EntireColumn.Hidden = TrueNot sure that I follow exactly what’s needed, but it sounds like the OFFSET() function would probably be useful in the formula – to return #N/A if the applicable cell below contains “AL” ??
-
RetiredGeek
AskWoody_MVPNovember 25, 2010 at 9:30 pm #1256717Frank,
Ok I think this fixes it.
-
WSColinBurrows
AskWoody LoungerNovember 26, 2010 at 10:34 am #1256751Just took another look at this. Attachment illustrates what I meant in my earlier post – about using a formula to drive whether or not the column is hidden. The reason for using #N/A, by the way, is that then you can use the xlErrors parameter of the SpecialCells function.
In the attached I made it that clearing inpCode (cell C7) will cause all columns to be unhidden. -
WSfrankwalsh1962
AskWoody Lounger -
RetiredGeek
AskWoody_MVPDecember 13, 2010 at 8:16 am #1258606Frank,
Here’s a modified version that picks up the value in the selected cell as the filter value so it will work for any type of leave.
-
WSWebGenii
AskWoody LoungerDecember 14, 2010 at 1:25 pm #1258762On my spreadsheet, I record annual leave, sick absence, training away-days etc for a whole department. I would like to be able to show only the columns with for example an ‘AL’ for annual leave on some given row, so I can easily see which dates are holiday for that person (row). This should work just like filtering rows on some value in one column. Excel won’t do this by itself, and copying, transposing and filtering is too clumsy. Any help TIA
FrankHave you investigated Custom Views, in 2007 they are on the View Ribbon. Does what I think you want without any code.
-
WSfrankwalsh1962
AskWoody LoungerDecember 17, 2010 at 10:07 am #1259244Hello Catherine, thanks for this but custom views won’t do the job. They only save a pre-chozen set of show/hide columns and I need to have excel choose which ones to hide according to changing criteria.
I’m still trying with the suggestions from RG and may have time to try again over the weekend. Thanks Frank -
WSColinBurrows
AskWoody LoungerDecember 17, 2010 at 10:39 am #1259250Hi Frank,
I’m curious as to what didn’t work with the attachment in my last post (Attendance.xlsm). I noticed that I saved it as an Excel 2007 workbook, but presumbaly you would have said if that was the problem.
Quite often something can be [primarily] accomplished with either Excel formulas or with VBA code. I tend to favour the former, since it’s generally more maintainable/understandable to end users. And also more visible, of course.
Anyway, what I posted *does* have a kind of funky formula in the row at the top. It’s designed to return #N/A if that particular column should be hidden. The VBA code itself is then very short (and runs quickly), since it’s just initially unhiding all the columns and then hiding just those columns where the aforementioned formula evaluates to #N/A. I say quicker because it does the hiding all at once – without having to iterate through the columns (no offense, RG!).
Colin -
RetiredGeek
AskWoody_MVPDecember 17, 2010 at 8:53 pm #1259403Colin,
None Taken. Another view is always welcome!
-
WSfrankwalsh1962
AskWoody LoungerDecember 22, 2010 at 9:19 am #1260081Hello Colin,
I can see how your solution works now. I was not able to unhide all columns after using the refresh button, by (I thought) keeping the name field blank – hoping to restore the sheet to it’s original state.
I can always just close without saving of course. One useful feature is that the columns either side of the dated columns block are unaffected – I shall shortly upload a dummey example showing why this is good.Wishing you all a happy holiday season.
Frank
Viewing 12 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Pwn2Own Berlin 2025: Day One Results
by
Alex5723
2 minutes ago -
Windows 10 might repeatedly display the BitLocker recovery screen at startup
by
Susan Bradley
55 minutes ago -
Windows 11 Insider Preview Build 22631.5409 (23H2) released to Release Preview
by
joep517
2 hours, 11 minutes ago -
Windows 10 Build 19045.5912 (22H2) to Release Preview Channel
by
joep517
2 hours, 13 minutes ago -
Kevin Beaumont on Microsoft Recall
by
Susan Bradley
9 hours, 14 minutes ago -
The Surface Laptop Studio 2 is no longer being manufactured
by
Alex5723
10 hours, 20 minutes ago -
0Patch, where to begin
by
cassel23
4 hours, 22 minutes ago -
CFPB Quietly Kills Rule to Shield Americans From Data Brokers
by
Alex5723
23 hours, 58 minutes ago -
89 million Steam account details just got leaked,
by
Alex5723
11 hours, 44 minutes ago -
KB5058405: Linux – Windows dual boot SBAT bug, resolved with May 2025 update
by
Alex5723
1 day, 8 hours ago -
A Validation (were one needed) of Prudent Patching
by
Nibbled To Death By Ducks
23 hours, 30 minutes ago -
Master Patch Listing for May 13, 2025
by
Susan Bradley
10 hours, 37 minutes ago -
Installer program can’t read my registry
by
Peobody
5 hours, 35 minutes ago -
How to keep Outlook (new) in off position for Windows 11
by
EspressoWillie
21 hours, 17 minutes ago -
Intel : CVE-2024-45332, CVE-2024-43420, CVE-2025-20623
by
Alex5723
1 day, 4 hours ago -
False error message from eMClient
by
WSSebastian42
1 day, 19 hours ago -
Awoke to a rebooted Mac (crashed?)
by
rebop2020
2 days, 4 hours ago -
Office 2021 Perpetual for Mac
by
rebop2020
2 days, 5 hours ago -
AutoSave is for Microsoft, not for you
by
Will Fastie
1 day, 2 hours ago -
Difface : Reconstruction of 3D Human Facial Images from DNA Sequence
by
Alex5723
2 days, 9 hours ago -
Seven things we learned from WhatsApp vs. NSO Group spyware lawsuit
by
Alex5723
1 day, 10 hours ago -
Outdated Laptop
by
jdamkeene
2 days, 14 hours ago -
Updating Keepass2Android
by
CBFPD-Chief115
2 days, 20 hours ago -
Another big Microsoft layoff
by
Charlie
2 days, 19 hours ago -
PowerShell to detect NPU – Testers Needed
by
RetiredGeek
17 hours, 21 minutes ago -
May 2025 updates are out
by
Susan Bradley
1 hour, 13 minutes ago -
Windows 11 Insider Preview build 26200.5600 released to DEV
by
joep517
3 days, 2 hours ago -
Windows 11 Insider Preview build 26120.3964 (24H2) released to BETA
by
joep517
3 days, 2 hours ago -
Drivers suggested via Windows Update
by
Tex265
3 days, 1 hour ago -
Thunderbird release notes for 128 esr have disappeared
by
EricB
21 hours, 57 minutes ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.