Change Event

[ToddEZ]

The goal is to have rows with a "" in cells B7-B90 automatically hide when I
change cell B5. I use cell B5 to enter an ID number and cells B7-B90
populate based on vlookup and index functions.

I have the following code in a worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(2).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Then cell.EntireRow.Hidden = True
Next cell
End With
End Sub

The macro was working fine, then I renamed the workbook and it dosen't work.

Any suggestions?

 

[Don Guillett]

I can't see why changing the name of the workbook would matter.
Doesn't work is not terribly descriptive.
Calculation set to manual?

This may be a bit quicker.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
.Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

' For Each cell In .Columns(2).SpecialCells(xlCellTypeFormulas)
' If cell.Text = "" Then cell.EntireRow.Hidden = True
' Next cell
End With
End Sub

 

[ToddEZ]

Apparently the issue is not that I renamed it, I added a password to the file
(using excel 2007). Having a password seems to have disabled the macro.

I'd like to keep the password on the file.

How do I get around this?

 

[Ron de Bruin]

See this page Tod
http://www.rondebruin.nl/password2007.htm

 

[Rick Rothstein]

Not sure if this would necessarily be quicker, but (see my comments after
the code)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
Dim FirstAddress As String
If Target.Address = "$B$5" Then
On Error GoTo FixItUp
Application.ScreenUpdating = False
Set C = Range("B7:B90").Find("", LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.EntireRow.Hidden = True
Set C = Range("B7:B90").FindNext(C)
Loop While Not C Is Nothing
End If
End If
FixItUp:
Application.ScreenUpdating = True
End Sub

By the way, why isn't your code monitoring cell B5 for change? As written,
it will hide the rows for any change made on the worksheet, not just a
change in B5. Also, your code will affect rows of data outside of the range
B7:B90... it will hide the rows if there is blank cells in Column 2 before
B7 or if there are blank cells in Column 2 after B90 provided there is data
in Rows after them. Oh, and you forgot to include this statement at the end
of your code...

Application.ScreenUpdating = True

and I would handle it using an On Error GoTo trap similar to what I did
above.