Excel VBA: Hide Rows When an IF Formula Returns Blank
Question details
The user needs to hide spreadsheet rows when an IF formula returns an empty string so printed letters do not contain large blank areas.
- Product
- Microsoft Excel with VBA
- Device & OS
- not provided
- Scenario
- A worksheet uses formulas and control cells to generate letters or documents, and rows with blank formula results should be excluded before printing.
- Observed behavior
- Rows remain visible even when their formulas return an empty string, leaving blank spaces in the printed output.
Identify the control column or cells that determine whether each row should appear, and note the worksheet name and row range the macro must process. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) before adding VBA.
Use Excel VBA to hide rows with blank formula results
Use this solution when the worksheet must automatically hide rows based on an IF result or a control value such as No.
The macro can inspect the controlling cells and hide a row when the value is No or the formula result is an empty string. Change the worksheet name, control column, and row range to match your workbook.
In Excel, open the workbook and press Alt+F11, then choose Insert > Module in the Visual Basic Editor.
Paste a macro such as `Sub HideBlankRows() Dim ws As Worksheet Dim r As Long Set ws = ThisWorkbook.Worksheets("Sheet1") For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ws.Rows(r).Hidden = (ws.Cells(r, "A").Value = "No" Or ws.Cells(r, "B").Value = "") Next r End Sub` and replace Sheet1, column A, and column B with the correct worksheet and columns.
Return to Excel, select Developer > Macros, choose HideBlankRows, and click Run. Rows whose control value is No or whose formula result is blank will be hidden.
To restore the worksheet, select the affected rows, right-click the row headers, and choose Unhide. You can also create a second macro that sets `ws.Rows.Hidden = False` for the target range.
Use Word Mail Merge for conditional letters
Use this approach when the final output is a letter and content should be included or excluded without leaving hidden or blank spreadsheet rows.
Create clean, compatible spreadsheets and letters with WPS Office
WPS Office is a free, lightweight alternative for working with Excel and Word files. Its familiar interface supports common XLSX and DOCX workflows, making it easier to migrate spreadsheets, prepare data, and create polished documents without changing your file formats.
Frequently Asked Questions
Why does Excel not hide a row when an IF formula returns ""?
An IF formula that returns an empty string displays as blank, but Excel does not automatically change the row's Hidden property. A VBA macro or manual filtering step is required.
How can I hide rows when a control cell contains No?
Use a VBA condition such as `ws.Cells(r, "A").Value = "No"` inside a loop, then set `ws.Rows(r).Hidden = True` for matching rows. Update the column reference and row range for your sheet.
How do I show the hidden rows again after printing?
Select the affected row headers, right-click, and choose Unhide. A macro can also restore them by setting the target range's `Hidden` property to False.
Can Word Mail Merge avoid blank rows in generated letters?
Yes. Use Mailings > Rules > If...Then...Else to conditionally include or exclude text based on an Excel field. This keeps unwanted content out of the letter instead of hiding spreadsheet rows.
