VBA & Macro Problems

How to Fix an Excel VBA Macro That Hides Rows and Columns Incorrectly

Maira Mehtab Sep 30, 2026 868 views

Question details

The user needs to fix a VBA macro designed to hide blank rows and columns, which is currently malfunctioning due to coding or reference errors.

How to Fix an Excel VBA Macro That Hides Rows and Columns Incorrectly

Product: Excel
Device & OS: not provided
Scenario: Automating the process of hiding blank rows and columns in a worksheet using a VBA macro.
Observed behavior: The macro hides rows and columns incorrectly or fails to run due to wrong variable types, inaccurate last-used-row/column calculations, or incorrect worksheet references.

Before you start

Before modifying your VBA code, ensure you save a backup copy of your Excel workbook to prevent accidental data loss or irreversible formatting changes during testing.

Solution 1Recommended

Optimize and Correct the VBA Macro Code

Update the macro to properly unhide existing rows, use Long variables for accurate counting, and find empty ranges using the CountBlank function.

When a macro uses Integer for row variables, it can cause overflow errors on modern Excel sheets that support over a million rows. Additionally, failing to explicitly reference the worksheet or failing to reset the hidden state of cells beforehand can lead to unpredictable and incorrect results.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor, and locate your specific macro code inside the Project Explorer.

Declare variables as Long

Change any row or column counters in your declarations from Integer to Long (e.g., Dim LastRow As Long). This prevents overflow errors when processing large datasets.

Reset hidden rows and columns

Add code at the very beginning of your procedure to unhide all cells first. Use Cells.EntireRow.Hidden = False and Cells.EntireColumn.Hidden = False to ensure a clean slate.

Explicitly reference the worksheet

Ensure your code points to the exact sheet instead of relying on the active sheet. Wrap your logic in a With statement, such as With ThisWorkbook.Sheets("Product").

Use CountBlank to detect empty cells

Implement Application.WorksheetFunction.CountBlank(Range) within your loop to accurately identify rows or columns that contain absolutely no data, and set their Hidden property to True.

Accurate Last Row Calculation: Make sure to calculate the last used row dynamically using a reliable method, such as: LastRow = Cells(Rows.Count, "A").End(xlUp).Row.

Solution 2

Sanitize Data and Debug with a Test Workbook

Isolate the issue by stepping through the macro in a simplified workbook without sensitive data to identify formula or referencing conflicts.

Run and Debug Macros Smoothly in WPS Spreadsheet

WPS Office offers robust built-in support for VBA and macros, allowing you to seamlessly run, edit, and troubleshoot scripts for automating complex tasks like hiding empty rows and columns.

1. Open your macro file in WPS: Launch WPS Spreadsheet and open your existing .xlsm or .xls file containing the macro.
2. Access Developer Tools: Navigate to the 'Tools' tab on the top ribbon and click on 'Developer' to access macro functionalities.
3. Open the VBA Editor: Click on 'Macros' or the 'VBA Editor' icon to view, step through, and safely debug your row-hiding scripts.

Highly compatible with Microsoft Excel macro-enabled files (.xlsm)Built-in advanced developer tools for writing and debugging VBA scriptsLightweight application that processes large automated datasets quicklyFree and user-friendly interface identical to familiar spreadsheet software

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an overflow error when running a row-hiding macro?

Overflow errors typically occur when you declare row variables as 'Integer'. Excel worksheets contain over 1 million rows, which exceeds the maximum limit of the Integer data type (32,767). Declaring your variables as 'Long' resolves this issue.

How does CountBlank work differently than IsEmpty in VBA?

IsEmpty only checks if a cell is completely uninitialized. If a cell contains a formula that returns an empty string (""), IsEmpty will return False. WorksheetFunction.CountBlank evaluates the actual output, correctly identifying cells that visually appear blank even if they contain formulas.

Why is my macro hiding rows on the wrong worksheet?

If your VBA code uses generic references like 'Range' or 'Cells' without specifying the sheet, it defaults to the ActiveSheet. To fix this, explicitly reference the target sheet using a With statement, such as With Sheets("Product"), and ensure you use a period before your range objects (e.g., .Cells).

Excel & Data Problems