How to Use VBA Macro to Unhide Specific Columns and Rows on Worksheet Activate
Question details
The user needs a VBA macro to automatically hide all rows and columns, and then selectively unhide specific rows and columns when a particular worksheet is activated.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating worksheet visibility to display only a specific range of data (rows 1-18, columns A-CZ) immediately upon opening or activating the worksheet.
- Observed behavior
- The original code failed because it used undeclared range variables and did not explicitly reference entire rows and columns to apply the hidden property correctly.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled so you can access the Visual Basic for Applications (VBA) editor.
Use Explicit Range Declarations in the Worksheet_Activate Event
This method ensures that row and column ranges are explicitly defined and modified, preventing errors caused by undeclared variables when the worksheet is activated.
To make the macro reliable, you must declare your variables using the 'Dim' statement and explicitly tell Excel to target the 'EntireRow' and 'EntireColumn' properties. Placing this code in the specific worksheet's module ensures it triggers automatically whenever that sheet is activated.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet application.
In the Project Explorer pane on the left side of the VBA editor, find your workbook project. Double-click the specific worksheet name (e.g., 'Sheet1' or 'Data') where you want this event to trigger.
Copy and paste the following code into the blank code window: Private Sub Worksheet_Activate() Dim rowRange As Range, colRange As Range Rows.Hidden = True Columns.Hidden = True Set rowRange = Rows("1:18") Set colRange = Columns("A:CZ") rowRange.EntireRow.Hidden = False colRange.EntireColumn.Hidden = False Application.Goto Range("A1") End Sub
Close the VBA editor and save your file. To test the macro, switch to a different worksheet and then click back to your target worksheet. The macro will hide all cells and instantly unhide only rows 1-18 and columns A-CZ.
Run Macros and VBA seamlessly in WPS Office
WPS Office Spreadsheet provides excellent, built-in support for VBA macros. You can automate your workflow, control column visibility, and run complex scripts just as you would in Microsoft Excel.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
- 2. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm file containing the VBA code.
- 3. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to edit, debug, and run your Worksheet_Activate macros seamlessly.
Frequently Asked Questions
Why is my Worksheet_Activate macro not running automatically?
There are two common reasons: macros might be disabled in your Trust Center settings, or the code was placed in a standard Module instead of the specific Worksheet object module. Ensure you double-click the sheet name in the Project Explorer before pasting the code.
Can I use this VBA code for different row and column ranges?
Yes, the code is fully customizable. You can modify the string values in Rows("1:18") and Columns("A:CZ") to match the exact ranges you want to remain visible on your worksheet.
How can I unhide all columns without using a VBA macro?
To unhide manually, click the small triangle in the top-left corner of the worksheet to select all cells, right-click any column header letter (like A or B), and choose 'Unhide' from the context menu.
