How to Hide Blank Columns in a Filtered Range using Excel VBA Macro
Question details
The user wants to use a VBA macro to automatically hide columns that contain no values greater than or equal to 1 in the visible rows after applying a filter.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering a large dataset leaves some columns completely empty or with a visible sum of zero. The user needs to automatically hide these irrelevant columns to clean up the current data view.
- Observed behavior
- When a row filter is applied, columns that have no relevant data in the remaining visible rows stay visible, taking up screen space and making the spreadsheet difficult to read.
Ensure you have the Developer tab enabled in your spreadsheet application and remember to save your file as a Macro-Enabled Workbook (.xlsm) so your VBA code is not lost upon closing.
Use a VBA Macro to Evaluate and Hide Columns by Visible Sum
Create a macro that unhides all target columns, loops through them to sum only the visible cells, and hides any column where the sum is zero.
This approach uses the SpecialCells(xlCellTypeVisible) method. It ensures that the macro only calculates data currently visible on the screen, ignoring rows that have been hidden by the filter.
Press the Alt + F11 keys on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the top menu, click on 'Insert' and then select 'Module' to create a blank workspace for your macro code.
Start your subroutine and add a command to unhide all columns in your target range first, for example: Columns("A:Z").Hidden = False.
Create a 'For Each' loop to iterate through each column in your target range. Inside the loop, use 'WorksheetFunction.Sum(col.SpecialCells(xlCellTypeVisible))' to calculate the sum of the visible cells.
Add an 'If' statement inside the loop. If the calculated visible sum is less than 1 (or exactly zero), set 'col.EntireColumn.Hidden = True'. Run the macro by pressing F5.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers robust support for VBA macros, enabling you to automate repetitive tasks like hiding blank columns in filtered datasets. With a highly intuitive interface and seamless Microsoft Excel compatibility, you can run your existing macros without having to rewrite any code.
- 1. Enable Developer Tools: Open WPS Spreadsheet, go to the Options menu, and ensure the Developer tab is enabled to access macro features.
- 2. Open the Macro Editor: Navigate to the Developer tab on the top ribbon and click on 'Visual Basic Editor'.
- 3. Insert and Run Your Code: Paste your VBA code to hide blank columns into a new module, save the workbook as an .xlsm file, and run it directly from the Macros menu.
Frequently Asked Questions
Why does my macro hide columns that still contain data?
This happens if your VBA code is summing the entire column rather than just the visible cells. Make sure you are using the 'SpecialCells(xlCellTypeVisible)' property so the macro ignores rows hidden by the filter.
How can I easily unhide the columns again?
You can create a very short secondary macro with the code 'Columns.Hidden = False' and assign it to a 'Reset' button. Clicking it will instantly make all hidden columns visible again.
Do I need to save my file in a specific format after adding this macro?
Yes. Spreadsheets containing VBA code must be saved as a Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, the application will remove your macro code permanently.
Does WPS Office support Excel VBA macros?
Yes, WPS Office supports VBA macros. You can open your Excel .xlsm files in WPS Spreadsheet, and access the Visual Basic Editor to create, edit, and run macros just as you would in Microsoft Excel.
