On occasion, you might find yourself creating a spreadsheet that has multiple columns all set up in a consistent format (i.e. quarterly sales figures for the past 5 years). As time goes on, you may add/remove data to the spreadsheet as needed. This may result in some columns not being used (i.e. in April only the first quarter’s information will be filled out for the current year, leaving the second, third and fourth quarters blank).
Using the quarterly sales example above, assume that you’ve taken the time to set up your spreadsheet with placeholder columns for the remainder of the year. When printing the information, you don’t want to just delete the columns, only having to recreate them in the future, and you also don’t want to have to manually hide/unhide them either.
You can use a macro to toggle between hiding and unhiding these empty columns fairly easily. The first bit of code will determine if the total in row 8 for columns B through M is 0. If it is, it will hide the column, otherwise it will make sure the column is not hidden. The second bit of code will select columns B through M and make sure they all are not hidden, so that you can toggle between hiding the unused columns, and showing all the columns.
To use this in your own spreadsheet, you may need to change a few things. 1) Change the Range reference (i.e. “B8:M8”) to whatever range you want evaluated. 2) Change “Columns” in Rng = Selection.Columns.Count to “Rows” if the range you are evaluating is a range of rows not columns. 3) If the criteria is something other than “0”, change the “0” in If ActiveCell = 0 to whatever that criteria happens to be. And finally 4) the two lines that say ActiveCell.Offset(0, 1).Select simply moves the selected cell down 0 cells and to the right one cell. If you’re evaluating down a range of rows, change the 0 to a 1 and the 1 to a 0 so that it moves the selected cell down 1 cell and to the right 0 cells. (Note you can move up a cell by inserting a -1 as the first number and to the left by inserting a -1 as the second number).
Need help? Use our nifty guide to help figure out how to install and use your macros.
Hide Unused Columns:
Unhide all Columns: