Excel Zoom

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:

Sub hide_unused()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
Range("B8:M8").Select
Rng = Selection.Columns.Count
For i = 1 To Rng
If ActiveCell = 0 Then
Selection.EntireColumn.Hidden = True
ActiveCell.Offset(0, 1).Select
Else: Selection.EntireColumn.Hidden = False
ActiveCell.Offset(0, 1).Select
End If
Next i
Application.ScreenUpdating = True
End Sub
Unhide all Columns:

Sub unhide_all()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
Columns("B:M").Select
Selection.EntireColumn.Hidden = False
Application.ScreenUpdating = True
End Sub


Sometimes we hide certain rows or columns because we don’t want the data to print, or just to get it out of the way. Other times though we simply don’t want to share the data in a hidden row or column. Assuming that the data in visible cells isn’t dependent on the data in hidden cells (i.e. if a formula references a hidden cell), we can use the macro below to automatically delete any hidden rows and columns in the active worksheet.

If you only want the hidden rows to be deleted, remove the three lines starting with For Ip = 256 through Next. To only delete the hidden columns, remove the next three lines starting with For lp = 65536 through Next.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub DeleteHiddenRowsColumns()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next

For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
Application.ScreenUpdating = True
End Sub