NeuroAgent

Auto-Rehide Columns After Full Selection in Excel VBA

Learn how to automatically restore hidden columns in Excel when users select all cells and resize columns using VBA events. Complete guide with code examples.

#column-management#excel-tips#excel-vba-automation#vba-programming#worksheet-events
Question

How can I automatically rehide columns that become visible when a user selects all cells and resizes a column in a spreadsheet?

When a user selects all cells (using Ctrl+A or clicking the select-all box) and then resizes any column, all previously hidden columns become visible. While we’ve provided a button to rehide these columns, some users struggle with this manual process.

Is there a way to detect when this column resizing occurs after a full selection and automatically rehide the columns that were previously hidden, without requiring any user interaction?

NeuroAgent

The best solution is to use Excel VBA events to detect when a user selects all cells and resizes a column, then automatically rehide the previously hidden columns. You can achieve this by implementing a worksheet selection change event combined with a dictionary to track which columns were originally hidden.

Contents

Understanding the Problem

When users select all cells in Excel (using Ctrl+A or clicking the select-all box in the top-left corner between row and column headers) and then resize any column, Excel automatically unhides all previously hidden columns. This behavior occurs because selecting all cells includes hidden columns in the selection, and resizing affects all selected columns.

The challenge is to detect this specific sequence of events and automatically restore the original hidden column state without requiring manual user intervention. According to Stack Overflow research, this is a common Excel behavior that many users struggle with.

Solution 1: Worksheet_SelectionChange Event

The most straightforward approach uses the Worksheet_SelectionChange event to detect when cells are selected and automatically rehide columns that should remain hidden.

vba
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Check if selection is the entire worksheet (all cells selected)
    If Target.Address = "$A$1:$IV$65536" Or Target.Address = "$A$1:$XFD$1048576" Then
        ' Store current hidden state before unhiding occurs
        Call StoreOriginalHiddenState
        
        ' Optionally: Add a small delay to allow resize operation to complete
        Application.Wait Now + TimeValue("0:00:01")
        
        ' Restore original hidden columns
        Call RestoreHiddenColumns
    End If
End Sub

Supporting Procedures

vba
' Module-level variable to store hidden column information
Private m_HiddenColumns As Collection

' Procedure to store which columns are currently hidden
Private Sub StoreOriginalHiddenState()
    Set m_HiddenColumns = New Collection
    Dim col As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    ' Check all columns in the worksheet
    For col = 1 to 16384 ' Excel has 16,384 columns
        If ws.Columns(col).Hidden = True Then
            m_HiddenColumns.Add col
        End If
    Next col
End Sub

' Procedure to restore previously hidden columns
Private Sub RestoreHiddenColumns()
    If m_HiddenColumns Is Nothing Then Exit Sub
    
    Dim col As Variant
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    ' Hide all columns that were originally hidden
    For Each col In m_HiddenColumns
        ws.Columns(col).Hidden = True
    Next col
    
    ' Clear the collection for next use
    Set m_HiddenColumns = Nothing
End Sub

Solution 2: Workbook Events with Column Tracking

A more robust solution uses workbook-level events and a class module to manage hidden columns more efficiently.

Step 1: Create a Class Module (cHiddenColumnManager)

vba' In a new class module named "cHiddenColumnManager"
Private m_HiddenColumns As Collection

Public Property Get HiddenColumns() As Collection
    Set HiddenColumns = m_HiddenColumns
End Property

Public Sub Initialize()
    Set m_HiddenColumns = New Collection
    CaptureHiddenColumns
End Sub

Public Sub CaptureHiddenColumns()
    Set m_HiddenColumns = New Collection
    Dim ws As Worksheet
    Dim col As Long
    
    Set ws = ActiveSheet
    
    ' Capture currently hidden columns
    For col = 1 to 16384
        If ws.Columns(col).Hidden Then
            m_HiddenColumns.Add col
        End If
    Next col
End Sub

Public Sub RestoreHiddenColumns()
    If m_HiddenColumns Is Nothing Then Exit Sub
    
    Dim ws As Worksheet
    Dim col As Variant
    
    Set ws = ActiveSheet
    
    ' Restore hidden columns
    For Each col In m_HiddenColumns
        ws.Columns(col).Hidden = True
    Next col
End Sub

Step 2: Add Workbook Event Code

vba' In ThisWorkbook module
Private mo_HiddenColumnManager As cHiddenColumnManager

Private Sub Workbook_Open()
    Set mo_HiddenColumnManager = New cHiddenColumnManager
    mo_HiddenColumnManager.Initialize
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ' Check if entire sheet is selected
    If Target.Address = "$A$1:$IV$65536" Or Target.Address = "$A$1:$XFD$1048576" Then
        ' Allow time for resize operation
        Application.Wait Now + TimeValue("0:00:01")
        
        ' Restore hidden columns
        mo_HiddenColumnManager.RestoreHiddenColumns
    End If
End Sub

Implementation Steps

1. Open the VBA Editor

  • Press Alt+F11 to open the Visual Basic for Applications editor
  • Or go to Developer tab → Visual Basic

2. Add the Code

  • For Solution 1: Right-click your worksheet name in the Project Explorer → Insert → Module
  • For Solution 2: Create a class module and add workbook event code

3. Test the Implementation

  • Hide some columns manually
  • Select all cells (Ctrl+A)
  • Resize a column
  • Verify that hidden columns are automatically restored

4. Considerations

  • The delay (Application.Wait) may need adjustment based on system performance
  • Test thoroughly to ensure it doesn’t interfere with normal operations
  • Add error handling for production use

Testing and Troubleshooting

Common Issues and Solutions

Problem: Code doesn’t trigger when resizing columns

  • Ensure the worksheet code is in the correct worksheet module
  • Check that events are enabled (Tools → Options → General → Enable Events)

Problem: Too frequent triggering

  • Add additional conditions to check if columns were actually resized
  • Implement a timer-based approach instead of immediate execution

Problem: Performance impact

  • Limit the scope to specific worksheets if needed
  • Optimize the column checking loop

Testing Procedure

  1. Setup: Hide several columns in your worksheet
  2. Test Selection: Select all cells (Ctrl+A) - columns should remain hidden
  3. Test Resize: Select all cells and resize any column - hidden columns should automatically rehide
  4. Verify Normal Operation: Test regular cell selection and column resizing to ensure no interference

Alternative Approaches

Using Worksheet Change Events

vba
Private Sub Worksheet_Change(ByVal Target As Range)
    ' This could work if you can identify when columns are resized
    ' However, detecting column resize specifically is more complex
End Sub

Using Protection Settings

According to Excel Forum research, you can use sheet protection with specific settings:

vba
Sub ProtectSheetWithResize()
    ActiveSheet.Protect Password:="yourpassword", _
        UserInterfaceOnly:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
    
    ' Note: This doesn't prevent unhiding, but controls other aspects
End Sub

Hybrid Approach

Combine multiple event handlers for more comprehensive detection:

vba
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsEntireSheetSelected(Target) Then
        Application.OnTime Now + TimeValue("0:00:02"), "RestoreHiddenColumns"
    End If
End Sub

Private Sub Worksheet_Activate()
    ' Capture hidden state when worksheet is activated
    CaptureHiddenColumns
End Sub

Private Function IsEntireSheetSelected(Target As Range) As Boolean
    ' Check if selection covers entire worksheet
    Dim lastCol As Long, lastRow As Long
    lastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    IsEntireSheetSelected = (Target.Address = "$A$1" And _
                            Target.Rows.Count = Rows.Count And _
                            Target.Columns.Count = Columns.Count)
End Function

Sources

  1. Stack Overflow - Detecting column resizing after full selection
  2. Excel Forum - Allow resize columns and disable hide/unhide
  3. Stack Overflow - View and hide columns using VBA
  4. MrExcel - Worksheet Change Event to hide/unhide columns
  5. Stack Overflow - VBA hide columns on cell change

Conclusion

To automatically rehide columns that become visible when users select all cells and resize columns, you have several effective VBA approaches:

Key Takeaways:

  • The Worksheet_SelectionChange event is the most direct way to detect full sheet selection
  • Using a dictionary or collection to store hidden column states provides reliable tracking
  • Adding a small delay allows the resize operation to complete before restoring hidden columns
  • Workbook-level events offer more comprehensive coverage across multiple worksheets

Recommendations:

  1. Start with Solution 1 for simpler implementation and testing
  2. Use Solution 2 for more robust, production-ready applications
  3. Always test thoroughly to ensure no interference with normal Excel operations
  4. Consider adding user notifications when columns are automatically hidden

These solutions effectively solve the problem of hidden columns becoming visible during full-sheet selection and column resizing operations, providing a seamless user experience without requiring manual intervention.

How do I prevent Excel from unhiding columns when selecting all cells without using VBA?What's the difference between Worksheet_SelectionChange and Workbook_SheetSelectionChange events in Excel VBA?How can I optimize VBA code for restoring hidden columns to improve performance in large worksheets?Is there a way to notify users when columns are automatically hidden in Excel?How do I handle errors in VBA code that restores hidden columns in Excel?Can I create a custom ribbon button to manually trigger column restoration in Excel?
Ask NeuroAgent