PDA

View Full Version : Sleeper: Hide unhide rows



TGwilding
09-05-2005, 08:08 PM
I am almost finished with this code but I am ahving a slight problem:

The code is below


Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Worksheets("Sheet1").Range("B2").Value = MyState Then
GoTo Xit
Else:
Dim cell As Range
For Each cell In Range("B9:B38")
If cell.Value = "" Then cell.EntireRow.Hidden = True
Else: cell.EntireRow.Hidden = False
Next cell
End If
Xit:
MyState = Worksheets("Sheet1").Range("B2").Value
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub


The Gap between between If cell.Value and Next Cell is where I am having problems. I would like rows where if cell.Value is not equal to "", then unhide.

Any help would be appreciated.
thanks

TGwilding
09-05-2005, 08:46 PM
I am using the code below which seems to be working fine.



Private Sub workbook_Open()
Static MyState As Variant
MyState = Worksheets("Sheet1").Range("B2")
End Sub
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Worksheets("Sheet1").Range("B2").Value = MyState Then
GoTo Xit
Else:
Rows("9:39").Select
Selection.EntireRow.Hidden = False
Range("E9").Select
End If
If Worksheets("Sheet1").Range("B2").Value = MyState Then
GoTo Xit
Else:
Dim cell As Range
For Each cell In Worksheets("Sheet2").Range("B9:B38")
If cell.Value = "" Then cell.EntireRow.Hidden = True
Next cell
End If
Xit:
MyState = Worksheets("Sheet1").Range("B2").Value
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub


thanks

MWE
09-05-2005, 08:46 PM
how about

If cell.Value <> "" Then cell.EntireRow.Hidden = False