Hide Row macro works in 2002 but not 2003
 

Good Day, I have been from site to site to get an answer and I am stumped...
I am usiing the following code:

Private Sub Worksheet_Calculate()
Dim x As Integer
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False 'unhide everything
For x = 51 To 79
If Range("U" & x).Value = 0 Then
Rows(x).EntireRow.Hidden = True
End If
Next x
Application.ScreenUpdating = True
End Sub

The purpose and background is that the workbook has 9 sheets. This code is
being utilized on 2 of the sheets. The macro was created in Excel 2002 and
when i try the file in Excel 2003 I get screen flickers and the file crashes.
When i hit CTRL + Pause the program skips to the debugger and hilights either
End If or End Sub, i guess somewhere in the loop it has caused?

In the range "U51:U79" there is a formula (HLOOKUP - for sheete 5 & 7 ) that
will display a result of either a 1 or a 0 depending on what item is chosen
on sheet 1. If it is a 0 then i would like the macro to hide the rows that
are 0. Another note is that this macro works with the sheets all password
protected and i allow rows to be formated when I set the password. (i have
tried unprotecting the sheet in Excel 2003, still does not work)

Again the Worksheet works perfectly in Excel 2002 but crashes in Excel 2003.

Sorry for the long winded note but i was reading several other posts and
wanted to try to give as much info so we are not reposting Q&A about the
spreadsheet functions.

I hope I left enough info.
Thanks

Hide Row macro works in 2002 but not 2003
 

Gooser,

That code tested just fine for me in xl2003. Windows XP home. Try
runnning the code in break mode and see where the error occurs exactly

"Gooser555" wrote:

Good Day, I have been from site to site to get an answer and I am stumped...
I am usiing the following code:

Private Sub Worksheet_Calculate()
Dim x As Integer
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False 'unhide everything
For x = 51 To 79
If Range("U" & x).Value = 0 Then
Rows(x).EntireRow.Hidden = True
End If
Next x
Application.ScreenUpdating = True
End Sub

The purpose and background is that the workbook has 9 sheets. This code is
being utilized on 2 of the sheets. The macro was created in Excel 2002 and
when i try the file in Excel 2003 I get screen flickers and the file crashes.
When i hit CTRL + Pause the program skips to the debugger and hilights either
End If or End Sub, i guess somewhere in the loop it has caused?

In the range "U51:U79" there is a formula (HLOOKUP - for sheete 5 & 7 ) that
will display a result of either a 1 or a 0 depending on what item is chosen
on sheet 1. If it is a 0 then i would like the macro to hide the rows that
are 0. Another note is that this macro works with the sheets all password
protected and i allow rows to be formated when I set the password. (i have
tried unprotecting the sheet in Excel 2003, still does not work)

Again the Worksheet works perfectly in Excel 2002 but crashes in Excel 2003.

Sorry for the long winded note but i was reading several other posts and
wanted to try to give as much info so we are not reposting Q&A about the
spreadsheet functions.

I hope I left enough info.
Thanks

Hide Row macro works in 2002 but not 2003
 

Not sure how to run break mode. Did you try with fomula reference runing in
the cells the macro references

"ben" wrote:

Gooser,

That code tested just fine for me in xl2003. Windows XP home. Try
runnning the code in break mode and see where the error occurs exactly

"Gooser555" wrote:

Good Day, I have been from site to site to get an answer and I am stumped...
I am usiing the following code:

Private Sub Worksheet_Calculate()
Dim x As Integer
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False 'unhide everything
For x = 51 To 79
If Range("U" & x).Value = 0 Then
Rows(x).EntireRow.Hidden = True
End If
Next x
Application.ScreenUpdating = True
End Sub

The purpose and background is that the workbook has 9 sheets. This code is
being utilized on 2 of the sheets. The macro was created in Excel 2002 and
when i try the file in Excel 2003 I get screen flickers and the file crashes.
When i hit CTRL + Pause the program skips to the debugger and hilights either
End If or End Sub, i guess somewhere in the loop it has caused?

In the range "U51:U79" there is a formula (HLOOKUP - for sheete 5 & 7 ) that
will display a result of either a 1 or a 0 depending on what item is chosen
on sheet 1. If it is a 0 then i would like the macro to hide the rows that
are 0. Another note is that this macro works with the sheets all password
protected and i allow rows to be formated when I set the password. (i have
tried unprotecting the sheet in Excel 2003, still does not work)

Again the Worksheet works perfectly in Excel 2002 but crashes in Excel 2003.

Sorry for the long winded note but i was reading several other posts and
wanted to try to give as much info so we are not reposting Q&A about the
spreadsheet functions.

I hope I left enough info.
Thanks

Solution
 

Hi Ben
Not sure why you cannot recreate problem but i have come up with a solution
for my situation:

I changed the code to the following instead of Worksheet_Calculate() to:

Private Sub Worksheet_Activate()
Dim x As Integer

Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False 'unhide everything

For x = 51 To 61
If Range("U" & x).Value = 0 Then
Rows(x).EntireRow.Hidden = True
End If
Next x

Application.ScreenUpdating = True
End Sub

So now when I change my data on Sheet 1, I then goto Sheet 5 & 7 and the
rows hide when I "activate" or click on the sheet. As a side note this code
also works when the sheet is protected (as long as i check allow "format
rows"). I do not have to unprotect or run a sub routine to unprotect and then
protect.

And the code works in Excel 2002 too...............

Hope this helps others.