How to Hide Blank Rows Across Multiple Worksheets with VBA
Question details
The user needs a VBA macro to unhide rows 1 to 94, then hide rows 4 to 94 if column A is blank, across the first 12 worksheets.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating row visibility based on blank cells in a specific range across a batch of worksheets.
- Observed behavior
- Manual hiding and unhiding of rows across 12 sheets is required. A macro is needed to automate the exact workflow based on the condition of column A.
Before running the macro, ensure you have saved a backup copy of your workbook, as actions performed by VBA scripts cannot be undone using the standard Undo shortcut.
Use a VBA Loop to Unhide and Hide Specific Rows
This macro uses a loop to iterate through the first 12 worksheets, unhides rows 1-94, and then selectively hides rows 4-94 where column A is empty.
This script targets the worksheets strictly by their index order (1 through 12). If your workbook contains fewer than 12 worksheets, you will need to adjust the script to prevent errors.
Open your workbook and press ALT + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu bar, then select 'Module' from the dropdown list to create a blank script window.
Copy and paste the following code into the module window: Sub HideBlankRows() Dim worksheetIndex As Integer Dim rowIndex As Integer For worksheetIndex = 1 To 12 With Worksheets(worksheetIndex) .Rows("1:94").Hidden = False For rowIndex = 4 To 94 If .Range("A" & rowIndex).Value = "" Then .Rows(rowIndex).Hidden = True End If Next rowIndex End With Next worksheetIndex End Sub
Click anywhere inside the pasted code and press F5, or click the green 'Run' button in the toolbar, to execute the macro across your first 12 worksheets.
Automate Tasks with Macros in WPS Spreadsheet
WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to run, edit, and create custom scripts to automate tasks like hiding blank rows seamlessly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
- 2. Access the Developer Tab: Navigate to the 'Developer' tab on the ribbon menu. If it's not visible, enable it from the settings.
- 3. Launch the VBA Editor: Click 'Visual Basic' or press ALT + F11 to open the built-in code editor.
- 4. Run your Script: Insert a module, paste your row-hiding macro, and run it to instantly format your sheets.
Frequently Asked Questions
Can I undo a VBA macro after it runs?
Standard undo shortcuts (like Ctrl+Z) generally do not work for actions performed by a VBA macro. It is highly recommended to manually save a copy of your file before running a new script.
How do I apply this to all worksheets instead of just the first 12?
You can modify the script by replacing the 'For worksheetIndex = 1 To 12' loop with 'For Each ws In ThisWorkbook.Worksheets' and adapting the 'With' statement to cycle through every sheet in the workbook regardless of the count.
Why is my macro not running when I press F5?
Ensure that macros are enabled in your spreadsheet settings. Go to the Developer tab, click on Macro Security, and select 'Enable macros'. Also, verify that your file is saved in a macro-enabled format such as .xlsm.
How can I change the column being checked for blank values?
Locate the line reading '.Range("A" & rowIndex).Value = ""' in the script. Simply change "A" to your desired column letter, such as "B" or "C", to check a different column for blank values.
