To hide a range of columns with Excel VBA, set its Hidden property to True: use Columns("B:D").Hidden = True to hide columns B through D on the active worksheet. For a dependable macro, qualify the range with the worksheet you mean; set Hidden = False to show the columns again.
How the VBA syntax works
Excel’s Hidden property controls whether worksheet rows or columns are hidden. A column reference such as Columns("B:D") identifies whole columns B, C, and D, so it is suitable for setting the property. The range endpoints are inclusive: Columns("C:F") affects C, D, E, and F.
You can also use the Range object, provided you identify complete columns. For example, Range("B:D").EntireColumn.Hidden = True makes the intent explicit. If you start with a cell range, such as Range("B2:D20"), use EntireColumn to hide the full columns that contain those cells. Setting Range("B2:D20").Hidden directly is not the right way to hide whole columns: the target should span entire columns or rows.
The property takes a Boolean value:
Truehides the specified columns.Falseunhides them.
These assignments affect the worksheet’s display, not the underlying values or formulas. A hidden column remains part of the workbook and may still be referenced by formulas, charts, or other VBA code.
Hide a range on a specific worksheet
A short command without a worksheet qualifier acts on the active sheet. That can be fine for a quick task, but it is risky in a reusable macro: if another sheet is active when the code runs, the wrong columns may be hidden. Reference the worksheet explicitly instead.
The following procedure hides columns B through D on a worksheet named Data. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
Sub HideDataColumns()
Dim ws As Worksheet
' Target the worksheet in this workbook.
Set ws = ThisWorkbook.Worksheets("Data")
' Hide whole columns B through D.
ws.Columns("B:D").Hidden = True
End Sub
Put the procedure in a standard VBA module. In Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Return to Excel and run the macro from the Macro dialog or the editor. The workbook must be saved in a macro-enabled format, such as .xlsm, to retain VBA code.
ThisWorkbook means the workbook that contains the macro. If the code is stored in a personal macro workbook or add-in but should act on the workbook currently being used, that reference may not be right; select the intended workbook explicitly rather than assuming which one is active. The sheet name in the example must match the worksheet tab name exactly.
Hide only columns that meet a condition
For a fixed set of columns, a direct assignment is clearest. If the columns to hide change according to a value or a rule, use a loop and make the condition explicit. For example, the code below hides columns whose row 1 header says “Internal.” It checks columns B through H on the Data sheet.
Sub HideInternalColumns()
Dim ws As Worksheet
Dim colNum As Long
' Set the worksheet and inspect columns B through H.
Set ws = ThisWorkbook.Worksheets("Data")
For colNum = 2 To 8
If ws.Cells(1, colNum).Value = "Internal" Then
ws.Columns(colNum).Hidden = True
End If
Next colNum
End Sub
This example checks the exact header text, including spelling and spaces. If a heading may have extra spaces or different capitalization, adjust the condition to account for those variations. Also decide what should happen to columns that do not match: this macro leaves their current hidden or visible state unchanged. If you want every column in the inspected range to be reset first, set that range’s Hidden property to False before the loop, then hide the matches.
Unhide columns or toggle their state
To show a range again, assign False to the same worksheet’s columns. This standalone procedure makes columns B through D visible:
Sub ShowDataColumns()
Dim ws As Worksheet
' Target the same worksheet as the hide example.
Set ws = ThisWorkbook.Worksheets("Data")
' Make columns B through D visible.
ws.Columns("B:D").Hidden = False
End Sub
A toggle can be useful when all columns in the target range always share the same state. However, a multi-column range may contain a mixture of hidden and visible columns. In that case, reading Hidden for the whole range may not produce a simple True or False value, so applying Not to it can fail or behave unexpectedly. For predictable results, use separate Hide and Show procedures, or check and change each column individually.
Showing B:D does not reveal a hidden column outside that range. For instance, if A and E are also hidden, they remain hidden. To unhide all columns on a sheet, use ws.Columns.Hidden = False, but be aware this changes every column, not just the ones your macro hid.
Noncontiguous ranges and variable endpoints
You can hide separate column blocks in one assignment by listing them as a multiple-area range, for example ws.Range("B:D,F:F").EntireColumn.Hidden = True. This hides B, C, D, and F while leaving E unchanged. Another readable option is to make one assignment per block. When ranges are assembled from user input or worksheet values, validate the column references before using them; malformed addresses cause a VBA runtime error.
For a variable endpoint, use the Cells collection with numeric column indexes. For example, ws.Range(ws.Columns(2), ws.Columns(4)).Hidden = True identifies columns 2 through 4, or B:D. If the endpoint comes from the last used column, determine that endpoint carefully: the definition of “last used” depends on whether you mean the last cell containing a value, a formula, or formatting. A common approach based on a particular row will miss columns that contain data elsewhere.
In a loop, a numeric index is often easier to manage than building a string such as “B:D.” Excel column numbers begin at 1 for column A, 2 for B, and so on. This is especially helpful when a macro processes a range whose size is determined at run time.
Common errors and practical safeguards
If a hide command does nothing or raises an error, check the target and the workbook state before rewriting the code. Frequent causes include:
- Wrong active sheet: qualify the range with a worksheet variable, rather than relying on whichever tab happens to be active.
- Incorrect sheet name: confirm the worksheet tab’s name matches the string used in
Worksheets("Data"). - Partial range: use whole columns or append
EntireColumnwhen starting from cells. - Protected worksheet: sheet protection can prevent formatting changes such as hiding columns. Unprotect the sheet using the appropriate password and workbook policy, or change the protection settings appropriately.
- Hidden columns outside the target: setting B:D visible does not reveal A or E. Inspect the columns on either side of the visible range when a column seems to have disappeared.
- Macros unavailable: Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use desktop Excel.
Hiding is not a security measure. Anyone with access to the workbook may be able to unhide columns, inspect formulas, or otherwise access the data. Do not rely on hidden columns to protect confidential information. If a worksheet is protected, protection can restrict certain actions, but it should not be treated as a substitute for controlling access to the file or its data.
On Excel for Mac, VBA is available in the desktop app, though some Windows-specific automation features and interfaces differ. The basic worksheet Hidden property works for ordinary column hiding. Excel for the web does not run VBA, so a macro-based solution must be run in desktop Excel.
VBA compared with manual hiding and grouping
Use VBA when the same operation must be repeated, triggered by a button, or applied according to a rule. For a one-time change, the ribbon or a keyboard shortcut is usually quicker. Select one or more column headers, then use Home > Format > Hide & Unhide > Hide Columns. To show them again, select the columns on both sides of the hidden range and choose Home > Format > Hide & Unhide > Unhide Columns.
On Windows, Ctrl+0 hides selected columns; Ctrl+Shift+0 unhides columns in many configurations, although Windows settings can reserve that shortcut. On Mac, the corresponding Excel shortcuts are Command+0 to hide columns and Command+Shift+0 to unhide them. Ribbon commands remain an alternative if a shortcut is unavailable or behaves differently on a particular computer.
For columns readers should be able to expand and collapse themselves, consider Data > Group rather than simply hiding them. Grouping creates an outline control beside the columns. It is designed for interactive summaries, whereas Hidden = True just changes visibility. Grouping is also not a security feature, and it may not suit every layout. Choose the simplest approach that matches whether the change is occasional, automated, or meant to be user-controlled.
Frequently Asked Questions
How do I hide columns A to C with VBA?
Use Columns("A:C").Hidden = True on the intended worksheet. For a macro that should not depend on the active sheet, qualify it, for example ws.Columns("A:C").Hidden = True, after assigning ws to the correct worksheet.
How do I hide a column range on the active sheet?
Use ActiveSheet.Columns("B:D").Hidden = True. This is concise, but it acts on whichever worksheet is active when the line runs. Explicitly naming the worksheet is safer in macros that may be launched from another sheet.
Can I hide columns based on cell values?
Yes. Loop through the columns you want to check, read the relevant header or cell, and set that column’s Hidden property when the condition is met. Limit the loop to the intended columns and decide whether nonmatching columns should retain their current state or be made visible.
Why does setting Hidden on my range cause an error?
The range may cover only part of each column. Use a whole-column reference such as Columns("B:D"), or convert a cell range with Range("B2:D20").EntireColumn.Hidden = True. Also check that the correct worksheet is targeted and that it is not protected against the change.
Can I run this VBA in Excel for the web?
No. Excel for the web can preserve a workbook that contains macros, but it does not run VBA. Open the workbook in desktop Excel for Windows or Mac to execute the macro.
Related
- Excel Split Cell Value by Delimiter: Methods and Tips
- Excel Split Cells Into Multiple Columns
- Excel Split Cell Data Into Multiple Columns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Unique List: How to Create One ReliablyTo create a unique list with Excel VBA, read the source range, keep the first occurrence of each…
- Excel VBA: Check Whether a Dynamic Array Is AllocatedNo—VBA arrays are not objects, so If myArray Is Nothing is not a valid way to test one.…
- Excel VBA Select Case Examples: Syntax and Common UsesIn Excel VBA, Select Case chooses which block of code to run based on the value of an…
- Excel Macro to Pull Data From Other Worksheets SafelyYes. In Excel for Microsoft 365 on Windows, a VBA macro can read ranges from other worksheets and…
- Excel VBA InputBox Choices: Menus, Buttons, and FormsExcel VBA’s standard InputBox asks users to type a response; it does not create a dropdown or a…
- VBA Copy Paste Row: Excel Code and Common FixesTo copy an entire row to another row in Excel with VBA, use Rows(sourceRow).Copy Destination:=Rows(targetRow). For example, Worksheets("Data").Rows(5).Copy…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.