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
Application.ScreenUpdating = True
Application.EnableEvents = True
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).
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