To hide columns with a VBA macro in Excel for Microsoft 365 on Windows, put Columns("B:D").Hidden = True in a procedure and run it. This example hides columns B through D on the active worksheet; change the column letters to target a different range.
What the hide-columns macro does
Excel stores column visibility as a property called Hidden. Setting that property to True hides the selected columns; setting it to False makes them visible again. The macro changes the worksheet’s display, not its contents: values, formulas, formatting, and references remain in the workbook.
Here is a complete macro for columns B through D:
Sub HideColumnsBtoD()
Dim ws As Worksheet
' Work on the currently active worksheet.
Set ws = ActiveSheet
ws.Columns("B:D").Hidden = True
End Sub
The ws variable points to the active worksheet, and Columns("B:D") identifies the three entire columns. The range is inclusive, so B, C, and D are all hidden. If the workbook has several sheets, the macro acts only on the sheet that is active when you run it.
How to add and run the VBA macro
In desktop Excel for Windows, use these steps to add the code to a standard VBA module:
- Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z.
- Press Alt+F11 to open the Visual Basic Editor. If the Developer tab is visible, you can also choose Developer > Visual Basic.
- In the editor, choose Insert > Module, then paste the complete procedure into the module window.
- Return to Excel, activate the worksheet whose columns you want to hide, and press Alt+F8.
- Select
HideColumnsBtoDin the Macro dialog and choose Run.
To display the Developer tab if it is missing, use File > Options > Customize Ribbon and select the Developer check box. On a Mac, Excel also supports VBA macros. The Visual Basic Editor is available from Developer > Visual Basic, or from Tools > Macro > Visual Basic Editor in versions that show that menu. Mac keyboard shortcuts can vary by keyboard and system settings, so using the ribbon or menu is a dependable alternative.
Excel for the web does not create or run VBA macros. Open the workbook in the desktop application to use this code. The web version may preserve a macro-enabled workbook, but VBA execution requires desktop Excel.
Change which columns the macro hides
Replace the column reference with the letters or range that matches the worksheet. For example, use ws.Columns("F:F").Hidden = True for just column F, or ws.Columns("A:A,C:C,E:E").Hidden = True to hide non-adjacent columns. For a continuous range such as H through K, use ws.Columns("H:K").Hidden = True.
You can also make the target sheet explicit instead of relying on whichever sheet is active. This is useful when a macro is launched from a button or another sheet:
Sub HideReportColumns()
Dim ws As Worksheet
' Use the sheet named Report in this workbook.
Set ws = ThisWorkbook.Worksheets("Report")
ws.Columns("G:J").Hidden = True
End Sub
The sheet name must match the worksheet tab name exactly, including spaces. ThisWorkbook means the workbook containing the macro. If the worksheet is renamed or deleted, the procedure will need to be updated. This explicit reference avoids a common mistake: hiding columns on a different sheet just because that sheet happened to be active.
Hide a column by its header text
When a report’s columns move over time, hard-coded letters may hide the wrong data. A macro can search a header row for a label, then hide the column it finds. The example below searches row 1 for an exact, case-insensitive match to “Internal Notes.”
Sub HideInternalNotesColumn()
Dim ws As Worksheet
Dim headerCell As Range
' Search row 1 on the active worksheet for the header.
Set ws = ActiveSheet
Set headerCell = ws.Rows(1).Find( _
What:="Internal Notes", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If headerCell Is Nothing Then
MsgBox "Internal Notes was not found in row 1.", vbInformation
Else
ws.Columns(headerCell.Column).Hidden = True
End If
End Sub
If the label is absent, the macro reports that instead of trying to hide a column. If the same header appears more than once, Find returns one match, not every matching column; additional search logic is needed to handle duplicates. Change the searched row or header text to match the workbook’s layout. This approach is useful for repeatable reports, but it depends on consistent header wording.
Unhide columns and create a one-column toggle
To reverse the change in code, assign False to the same columns’ Hidden property. This procedure unhides every column on the active sheet:
Sub UnhideAllColumns()
Dim ws As Worksheet
' Make every column visible on the active worksheet.
Set ws = ActiveSheet
ws.Cells.EntireColumn.Hidden = False
End Sub
Unhiding all columns may reveal columns that were intentionally hidden for other reasons, so use a narrower reference if you only want to restore a particular range. For example, set ws.Columns("B:D").Hidden = False to show B through D.
A toggle is convenient when you want to show or hide one column repeatedly. For example, the following changes the visibility of column D each time it runs:
Sub ToggleColumnD()
Dim ws As Worksheet
' Flip the visibility of this single column.
Set ws = ActiveSheet
ws.Columns("D:D").Hidden = Not ws.Columns("D:D").Hidden
End Sub
This simple toggle is intended for one column with a clear visible or hidden state. A multi-column range can contain a mix of visible and hidden columns, so its overall Hidden value may not represent a straightforward all-or-nothing state. For a multi-column range, separate “hide” and “unhide” macros are easier to understand and less surprising.
Macro, manual hiding, or column grouping?
A macro is most useful when the same visibility change must be repeated, when a report needs a one-click presentation, or when a column is selected by a known header. For a one-time change, the normal Excel controls are simpler: select the column headings, right-click, and choose Hide. To show hidden columns, select the headings on both sides of the hidden area, right-click, and choose Unhide.
Grouping is another option when people need to expand and collapse detail columns themselves. Select the columns and choose Data > Group. Excel adds an outline control near the sheet headings so users can collapse or expand the group without running VBA. Grouping does not provide security, either; it is a navigation convenience. Choose a macro for repeatable automation, manual hiding for occasional edits, and grouping when workbook users should control the visibility interactively.
Common problems and how to fix them
- The macro is not listed in Alt+F8: Check that the code is in a standard module, that it begins with a public, parameter-free
Sub, and that the workbook is open in desktop Excel. A procedure placed in a worksheet code module or markedPrivatemay not appear in the ordinary Macro dialog. - The wrong sheet changes: The basic example uses
ActiveSheet. Activate the intended sheet first, or replace it with an explicit reference such asThisWorkbook.Worksheets("Report"). - The macro does not persist after saving: Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm). A standard.xlsxfile does not store VBA code; Excel may warn that macros will be removed if you save the code in that format. - Excel blocks the macro: Review the security warning and your organization’s macro policy. Only run code from a source you trust; do not enable all macros globally just to run one procedure.
- The columns appear unchanged: Confirm the correct worksheet is active and check that the column letters are correct. A hidden column is indicated by a gap or a boundary between neighboring column headings; the data itself has not been deleted.
Safety and what hiding does not do
Hiding columns is a display choice, not a way to protect confidential information. Someone with access to the workbook can unhide the columns, inspect formulas, or copy data. If information must be restricted, do not distribute it in a workbook merely because its columns are hidden; use appropriate access controls and share only the data recipients should receive.
Macros can also make changes that are difficult to reverse. Excel’s normal undo history does not undo VBA actions, which is why saving a backup copy first matters. Review the code before running it, especially if it came from an email or downloaded file. A short visibility macro is limited in scope, but a procedure can contain other actions as well.
Frequently Asked Questions
Can a macro hide multiple non-adjacent columns?
Yes. You can provide a comma-separated column reference, such as ws.Columns("A:A,C:C,E:E").Hidden = True. Check the result on a copy if the target list is long or generated dynamically.
Can I hide columns automatically when a workbook opens?
Yes. A workbook can run a procedure from the Workbook_Open event in the ThisWorkbook code module. That requires event code and macros to be permitted, so use it only when automatic hiding is appropriate and explain the behavior to workbook users.
Can I assign the macro to a button?
Yes. On Windows, use Developer > Insert and choose a Button under Form Controls, draw it on the sheet, then assign the macro. A button makes repeated use easier, but users still need a desktop version of Excel with macros enabled to run the VBA.
Will hidden columns be hidden when I print?
Normally, hidden worksheet columns are not included in the printed output. Check Print Preview after running a macro, since print areas, page setup, and other workbook settings can also affect what appears on the page.
Does hiding a column remove its formulas or affect calculations?
No. Hiding a column changes its visibility, not the stored cells. Formulas elsewhere can continue to refer to those cells, and unhiding the column reveals its contents again.
Related
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Macro Recorder on Softonic: Download and Safety GuideMacro Recorder is a desktop automation program that records mouse and keyboard actions and plays them back; Softonic…
- VBA InputBox Options: Types, Choices, and ExamplesVBA’s basic InputBox accepts a prompt and returns what the user types as text; it does not provide…
- Excel VBA: Show a UserForm When the Workbook OpensTo show an Excel VBA UserForm when a workbook opens, put a Workbook_Open event procedure in the ThisWorkbook…
- Excel VBA: Highlight the Active CellTo highlight the active cell in Excel for Microsoft 365 on Windows, put a Worksheet_SelectionChange event in that…
- VBA: Send Multiple Pictures in Outlook Email with ExcelYes. In Excel for Microsoft 365 on Windows, VBA can create an Outlook email and add several picture…
- Excel VBA Enum: How It Works and When to Use ItAn Excel VBA Enum is a named set of numeric constants: it lets code use readable labels such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.