You are here:

Excel/Hiding blank lines


I am using Excel 2010 on Windows 7.  I am trying to automate the hiding of lines that contain no data.  Each line (except the first line)in the daughter sheet is a copy of the same line in a mother sheet if and only if the cell B in the line contains a text word that matches cell B1 in the daughter sheet.
I tried using an answer you gave back in 2007 to introduce a view code to the sheet with a few tweaks as follows:

Private Sub Worksheet_Calculate()
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("B2:B400")
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cell In r
If cell.Value = "" Then
  cell.EntireRow.Hidden = True
  cell.EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

I introduced a numeric 1 in rows that had data and not in the blanks so that the If cell.value line had a numeric value to trigger it but also no result. Have I failed to set up the worksheet to carry out the process or is it not the same in Excel 2010 or just plain wrong ?

So I guess in the daughter sheet you have something like this (assuming the Mother sheet is named "Sheet1"


I put that in A2, then drag filled it over to D2 then select A2:D2 and drag filled down many rows. I had data in the mother sheet that would produce some matches agains the value I placed in B1 of the Daughter sheet.  .  The formula worked fine.  I had your code in the code module of the Daughter sheet.  It worked fine.  I changed the value in B1 of the daughter sheet and new rows were made visible and the old rows were hidden - all properly and as expected.

So my guess is that Instead of have "" in the last part of the formula you have something like " "   so that it produces a space.  If that is the case, then your test  

If cell.Value = "" Then

will not be passed because cell.value will either contain a string or it will contain " " which does not equal "" and thus no rows will be hidden.

At least you know there is nothing wrong with your code (if I have correctly interpreted the situation).  You now need to determine if I have correctly estimated the problem or it is something else - but in any event, the code will work if the conditions are properly satisfied (and the code is in the code module of the daughter sheet).

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.

[an error occurred while processing this directive]