excel hide empty columns automatically

Auto-Hide Empty Columns in Excel: A Simple Setup Guide

By Christopher Brown
AD

Are you tired of spreadsheets filled with endless blank columns? Do you want to present clean, organized data without the hassle of manually hiding empty columns in Excel? You're in luck! In this guide, we will explore a simple yet powerful method to automatically hide empty columns in Excel, saving you time and enhancing your work efficiency.

Before we dive into the step-by-step process, let's understand why you might need to hide empty columns. When working with large datasets, it's common to have blank columns after you've filtered or sorted your data. These empty columns can clutter your workbook and make it difficult to focus on the relevant information. By automating the process of hiding empty columns, you can maintain a tidy and professional-looking spreadsheet.

Understanding Excel's AutoFit Columns Feature

To automatically hide empty columns, we will utilize Excel's AutoFit Columns feature. This brilliant tool adjusts the width of columns to fit their content, which includes both visible and hidden data. By using a simple VBA (Visual Basic for Applications) script, we can ask Excel to AutoFit all columns, effectively hiding the empty ones.

AD
How to Hide or Unhide Columns and Rows in Excel
How to Hide or Unhide Columns and Rows in Excel
How to Hide Unused Cells in Excel (3 Quick Ways) - ExcelDemy
How to Hide Unused Cells in Excel (3 Quick Ways) - ExcelDemy
How to Hide or Unhide Columns and Rows in Excel
How to Hide or Unhide Columns and Rows in Excel

Before we proceed, ensure that your Excel version supports VBA. Most versions from Excel 2010 onwards should have this feature. If you're using an older version, you might want to consider upgrading for better functionality and improved security.

Enabling Developer Tab in Excel

To access the VBA editor, you'll need to enable the Developer tab in your Excel ribbon. Here's how to do it:

How to Hide Selected Columns in Excel (5 Useful Examples) - ExcelDemy
How to Hide Selected Columns in Excel (5 Useful Examples) - ExcelDemy
an excel spreadsheet showing the rows and numbers in each column, with two arrows pointing
an excel spreadsheet showing the rows and numbers in each column, with two arrows pointing
How to Use Excel VBA to Hide Columns Based on Cell Value (15 Examples) - ExcelDemy
How to Use Excel VBA to Hide Columns Based on Cell Value (15 Examples) - ExcelDemy
  • Right-click on any tab in the ribbon and select 'Customize the Ribbon…'
  • Check the box next to 'Developer' and click 'OK'

Now, you should see the Developer tab in your ribbon. Click on it to access the VBA editor, which is where we will create our script.

Creating the VBA Script

With the Developer tab enabled, follow these steps to create the VBA script for hiding empty columns:

AD
  1. Click on the 'Developer' tab in the ribbon
  2. In the 'Code' group, click on 'Visual Basic'
  3. In the 'Visual Basic for Applications' window, click on 'Insert' and then 'Module' to add a new module
  4. In the module, type the following script:

Sub HideEmptyColumns()

How to Hide and Unhide Columns in Excel (7 Methods) - ExcelDemy
How to Hide and Unhide Columns in Excel (7 Methods) - ExcelDemy
How to Hide Columns in Excel with Minus or Plus Sign: 2 Methods - ExcelDemy
How to Hide Columns in Excel with Minus or Plus Sign: 2 Methods - ExcelDemy
How to Hide Columns in Excel with Password (2 Easy Ways) - ExcelDemy
How to Hide Columns in Excel with Password (2 Easy Ways) - ExcelDemy
How to Unhide Columns in Excel (8 Methods) - ExcelDemy
How to Unhide Columns in Excel (8 Methods) - ExcelDemy
How to Hide Extra Columns in Excel - 4 Methods - ExcelDemy
How to Hide Extra Columns in Excel - 4 Methods - ExcelDemy
Using Excel VBA to Hide Columns Based on Criteria - 6 Examples - ExcelDemy
Using Excel VBA to Hide Columns Based on Criteria - 6 Examples - ExcelDemy

ActiveWindow.ActivePane.FreezePanes = False

Columns.AutoFit

AD

End Sub

This simple script first unfreezes any frozen panes, then it asks Excel to AutoFit all columns. Hiding empty columns is merely a side effect of AutoFitting columns to their content.

Using the VBA Macro to Automatically Hide Empty Columns

Now that you have created the VBA script, it's time to use it to automatically hide empty columns in your Excel sheet. Follow these steps:

AD

Method 1: Shortcut

  1. Place your cursor anywhere in your worksheet
  2. Press 'Alt + F8' to open the 'Macros' dialog box
  3. Select 'HideEmptyColumns' and click 'Run'

Method 2: Macro Button

  1. Click on the 'Developer' tab
  2. In the 'Controls' group, click on 'Insert' then 'Button (Form Control)'
  3. Click and drag to draw a macro button on your worksheet
  4. Right-click on the button and select 'Assign Macro…'
  5. Select 'HideEmptyColumns' and click 'OK'

Each time you press the macro button or use the shortcut, the VBA script will run, automatically hiding any empty columns in your active worksheet.

Fine-tuning the AutoFit Columns Feature

While the AutoFit Columns feature does an excellent job of hiding empty columns, it might not always provide the best layout for your data. For example, if you have a column with very long text or a formula that returns lengthy results, AutoFit might stretch the column width beyond what's comfortable for you. To combat this, you can add a line to your script to limit the maximum column width:

Columns.ColumnWidth = 10

Replace '10' with the maximum column width you prefer (in characters). This change will prevent any column from becoming wider than the specified width, even if the AutoFit feature wants it to.

Updating the VBA Script for Multiple Worksheets

If your workbook contains multiple worksheets and you want to hide empty columns in all of them at once, you can modify the VBA script as follows:

Sub HideEmptyColumnsAllSheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

ws.Activate

HideEmptyColumns

Next ws

End Sub

This updated script activates each worksheet in the workbook, runs the 'HideEmptyColumns' macro on it, and then moves on to the next worksheet. Now, you can automate the process of hiding empty columns in all worksheets with a single run or button press.

Congratulations! You now know how to automate the process of hiding empty columns in Excel, freeing up your time and helping you present clean, organized data. Embrace this newfound efficiency and watch your productivity soar. Happy hidden column hunting!

AD