Yes. In Excel for Microsoft 365 on Windows, VBA can set a column’s Hidden property according to the value in a cell. For example, a macro can hide column C when B2 contains “Hide”; you can run it on demand or make it update automatically when B2 changes.
What it means to hide a column from a cell value
A worksheet column is hidden as a whole, so VBA needs a cell whose value acts as a condition and a column that the code will show or hide. These can be separate: B2 might contain a status such as “Hide” or “Show,” while column C contains information to conceal when the status is “Hide.” The code checks B2, then assigns either True or False to the target column’s Hidden property.
This is different from hiding individual cells or making text invisible. A hidden column is omitted from the worksheet view, although its data remains in the workbook and can still be referenced by formulas. Hiding is therefore a display choice, not data protection. Anyone with access to the workbook may be able to unhide the column.
Before writing a macro, decide what the condition should mean. Should the column be hidden only when the cell says “Hide,” when it is blank, when it contains zero, or when a formula returns a particular result? A precise rule makes the macro easier to maintain and prevents unexpected visibility changes.
Create and run a basic macro
The following example checks B2 on a worksheet named Report. If its value is “Hide,” it hides column C; for any other text, including “Show,” it makes column C visible. The comparison ignores capitalization and removes spaces at the beginning or end.
In the Visual Basic Editor, choose Insert > Module and put this code in the new standard module. Change the worksheet name, selector cell, or target column to match your workbook. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.
Sub UpdateColumnVisibility()
Dim ws As Worksheet
Dim status As String
' Read the selector and apply the visibility rule.
Set ws = ThisWorkbook.Worksheets("Report")
status = UCase$(Trim$(CStr(ws.Range("B2").Value2)))
ws.Columns("C").Hidden = (status = "HIDE")
End Sub
On Windows, press Alt+F11 to open the Visual Basic Editor, or use Developer > Visual Basic. If the Developer tab is missing, turn it on through File > Options > Customize Ribbon and select Developer. To run the macro, place the cursor inside the procedure and press F5, or return to Excel and use Developer > Macros, select UpdateColumnVisibility, and choose Run.
The assignment sets the result directly rather than merely hiding the column. That matters: if B2 changes from “Hide” to “Show” and the macro runs again, the same line sets Hidden to False and restores column C. Running the macro repeatedly therefore leaves the sheet in the state described by the current selector value.
Make the column update when a cell changes
A standard macro runs only when a user or another procedure starts it. To respond immediately when someone edits B2, put an event procedure in the code module for the worksheet itself—not in a standard module. In the Visual Basic Editor’s Project pane, double-click the Report sheet and add this code there:
Private Sub Worksheet_Change(ByVal Target As Range)
' Ignore edits that do not include the selector cell.
If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub
' Hide column C only when B2 says Hide.
Me.Columns("C").Hidden = _
(UCase$(Trim$(CStr(Me.Range("B2").Value2))) = "HIDE")
End Sub
This event runs when a user changes a cell on that worksheet, including when a value is pasted into B2. The Intersect check keeps unrelated edits from running the visibility rule. If users can paste a block that includes B2, the code still responds because the changed range intersects the selector cell.
Worksheet events are tied to the sheet that contains the code. If the selector is actually on a different worksheet, put the event procedure in that sheet’s code module and qualify the target column appropriately. A procedure with the right name in a standard module will not act as a worksheet event.
Use a formula result as the condition
If B2 contains a formula, the manual macro can read its current result. However, Worksheet_Change does not run just because a formula recalculates: the formula cell’s contents have not been edited. For a selector that changes as a result of recalculation, use the worksheet’s Worksheet_Calculate event instead.
Put this procedure in the same worksheet code module. It checks B2 after a calculation and updates column C. Because recalculation may happen often, keep the procedure short and avoid putting unrelated work inside it.
Private Sub Worksheet_Calculate()
' Recheck the formula result after calculation.
Me.Columns("C").Hidden = _
(UCase$(Trim$(CStr(Me.Range("B2").Value2))) = "HIDE")
End Sub
Use either event according to how the selector changes. Worksheet_Change is suitable when a person edits or pastes a value into B2. Worksheet_Calculate is suitable when B2’s formula result can change after other cells are edited or formulas recalculate. If the cell can contain an Excel error such as #N/A, conversion to text may cause a VBA type mismatch; validate the cell with IsError before converting it in a production macro.
Apply rules to more than one column
You can check separate selector cells to control separate columns. For example, B2 can control C and D2 can control E. Add both assignments to one procedure so the rules are easy to find and run together:
Sub ApplyColumnRules()
Dim ws As Worksheet
' Set each column from its own selector cell.
Set ws = ThisWorkbook.Worksheets("Report")
ws.Columns("C").Hidden = _
(UCase$(Trim$(CStr(ws.Range("B2").Value2))) = "HIDE")
ws.Columns("E").Hidden = _
(UCase$(Trim$(CStr(ws.Range("D2").Value2))) = "HIDE")
End Sub
For many rules, consider keeping a clear map of selector cells and target columns, or writing a loop if the layout follows a consistent pattern. Avoid hiding a column that contains a selector the user needs to reach; if a control cell disappears along with its target, the sheet can become confusing to operate. You can place control cells in a permanently visible area or on a separate control sheet.
Choose between a macro, an event, and manual hiding
The right method depends on whether the visibility needs to react to changing data and whether the workbook’s users can run macros.
- Run a macro manually when a user deliberately refreshes the layout after changing inputs. It is simple to understand and does not run after every edit.
- Use Worksheet_Change when a user-entered value should immediately control visibility.
- Use Worksheet_Calculate when the selector is a formula result that can change during recalculation.
- Hide or unhide columns manually when the layout rarely changes or macros are not permitted. Select the relevant columns, right-click a column heading, and choose Hide; to restore them, select the surrounding headings, right-click, and choose Unhide.
Excel formulas and conditional formatting can calculate or display conditions, but they do not set a worksheet column’s Hidden property. Conditional formatting can change appearance; it cannot collapse the column. VBA, manual commands, or another automation tool that controls the workbook’s structure is needed for actual column hiding.
Common problems and how to fix them
- The macro does nothing: Check that the worksheet name in the code exactly matches the tab name, that the correct workbook is active, and that macros are enabled. Confirm the code is in a standard module for a manually run macro or the correct sheet module for an event.
- The column hides but will not show again: Make sure the code assigns the result of the condition, rather than setting
Hiddento True only. The example assigns True for “Hide” and False for other values. - Changing a formula does not trigger the event: A formula recalculation does not count as a direct edit to its result cell. Use
Worksheet_Calculatewhen recalculation is what changes the condition. - The comparison misses the expected text: Check for extra spaces, spelling differences, or a selector value other than text. The sample trims leading and trailing spaces and ignores letter case, but it does not treat words such as “Hidden” as equal to “Hide.”
- The macro reports an error on the selector: An error value in the cell cannot safely be converted with
CStrwithout first checking it. Decide whether an error should show or hide the column, then handle that case explicitly.
Also check whether another macro or event is changing the same column’s visibility afterward. If two procedures apply conflicting rules, the last one to run determines what the user sees. A single procedure that owns the rule is usually easier to troubleshoot.
Saving, compatibility, and visibility limits
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); saving it as a macro-free .xlsx file removes its VBA project. Excel may display a security warning when the file opens. Enable macros only for workbooks you trust and follow your organization’s security policy. A macro-enabled workbook does not guarantee that macros will be allowed in every environment.
Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros. Use desktop Excel for Windows or Mac to work with the VBA code. Excel for Mac supports VBA, though keyboard shortcuts and some interface details can differ; use the Developer tab’s Visual Basic command if the Windows shortcut does not match your keyboard. The column-hiding property and the basic worksheet event approach are VBA features rather than web-based formula features.
Finally, hidden columns are not a privacy or security control. They remain part of the workbook and may be unhidden, copied, or referenced. Remove sensitive information or use an appropriate access-control approach if the data must not be available to workbook recipients.
Frequently Asked Questions
Can an Excel formula hide a column automatically?
Not by itself. A worksheet formula returns a value, and conditional formatting changes appearance; neither directly changes whether a whole column is hidden. Use VBA for an automatic rule, or hide and unhide columns manually.
How do I hide a column when a cell is blank?
Change the condition so it tests whether the selector is empty, rather than comparing it with the word “Hide.” Decide whether a formula returning an empty string should count as blank; a length check after trimming can treat both an empty cell and whitespace-only text as blank. Put that test in the same kind of manual macro or worksheet event shown above.
Will the macro work if the cell contains a formula?
Yes, a manually run macro can read the formula’s current result. To update automatically when that result changes because of recalculation, use the worksheet’s Worksheet_Calculate event, not only Worksheet_Change.
Can I hide several columns from one cell?
Yes. Add an assignment for each target column in the macro, using the same selector value for all of them. If different columns have different rules, give each rule a clearly specified condition and test it with suitable values.
Can I undo a VBA hide operation with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, so Ctrl+Z will not reverse the macro’s change. Save a copy before running unfamiliar code, and use another macro or the Unhide command to restore visibility.
Related
- Excel Split Cell Into Multiple Cells: Methods That Work
- Excel Split Names in One Cell Into Two Cells
- Excel Split One Cell Into Multiple Rows: How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Select a Range Offset from the Active CellIn Excel VBA, use ActiveCell.Offset(rowOffset, columnOffset).Select to select a cell relative to the active cell; use Resize after…
- Excel Macro Recorder: How to Record a MacroIn Excel for Microsoft 365 on Windows, go to Developer > Code > Record Macro, give the macro…
- Excel VBA Get Worksheet Index: Methods and ExamplesIn Excel VBA, read a worksheet’s Index property to get its position in the workbook’s sheet-tab order: for…
- VBA Find a Value in a Column: Methods and ExamplesTo find a value in an Excel column with VBA, use the Range.Find method on the column and…
- Excel VBA UserForm Label Font Color: Set and ChangeTo change an Excel VBA UserForm label’s text color, set its ForeColor property. For example, in the UserForm’s…
- Excel VBA Like Operator: Pattern Matching ExplainedYes. In Excel VBA, the Like operator compares a string with a pattern, letting you test for text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.