Change Event
 

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?

Change Event
 

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).EntireR ow.Hidden = True

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddEZ" wrote in message
...
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?

Change Event
 

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?

"Don Guillett" wrote:

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).EntireR ow.Hidden = True

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddEZ" wrote in message
...
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?

Change Event
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"ToddEZ" wrote in message ...
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?

"Don Guillett" wrote:

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).EntireR ow.Hidden = True

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddEZ" wrote in message
...
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?

Change Event
 

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.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
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).EntireR ow.Hidden = True

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddEZ" wrote in message
...
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?